Power BI Append vs Merge

13 March 2023
Eugene Lebedev

Eugene Lebedev is a Power BI consultant by background. Before founding Vidi Corp in 2021 he created Power BI reporting for Autodesk. His Power Bi reports were used by VPs of Finance and C-suite of Autodesk. As part of Vidi Corp, Eugene created dashboards for Google, Teleperformance, Delta Airlines and 200+ clients worldwide.

This article will help you to understand and use Power BI Append vs Merge functions – some of the most powerful functions inside of Power Query.

Merge and append

Both of them allow you to join several datasets into one, but in Power BI Append vs Merge are quite different. In short, merge is used to bring new columns into the dataset whereas append is used to bring more rows. Let’s look into them individually to understand them better.  Including an explanation Power BI Append vs Merge video as well.

To see how you can optimise your reporting with Power BI, make an enquiry here. 

Power BI Merge

Basic Merge

Merge works like a Vlookup in Excel. In order to run the merge, you need 2 tables. Those 2 tables need to have 2 columns with matching values. I am going to use the Adventurewords2017 database in order to demonstrate this.

I have one table called HumanResources Department and one other table called HumanResources Employee. There is a column called Department ID in the Employee table.

basic merge in Power BI step 1

There is the same column inside of the department table

basic merge in Power BI step 2

Now suppose that you want to have your department name instead of department ID inside of the Employee table. In this case Merge is your friend!

Start your merge by pressing the “Merge Query” button inside the “Home” ribbon.

See also  5 ways ClickUp is Transforming Business Operations

basic merge in Power BI step 3

Once you press it, Power BI will bring up the next menu which will ask you to choose the second table for the merge. At this point you can select the Department table.

basic merge in Power BI step 4

You then need to select the columns based on which you want to do the merge. You can do this by left-clicking on those.

basic merge in Power BI step 5

The final thing for you to do is to select the merge type. Different merge types are a big topic that really deserves its own post. This is why I wrote one! You can read it here!

Finally, once you click ok, you will see a new column appearing on the right. This means that the merge was successful.

basic merge in Power BI step 6

You can click the “Expand” icon to see which columns you can now add into your dataset.

basic merge in Power BI step 7

If you put a checkmark near “use original column name as prefix”, your columns will come out with the prefix “HumanResources Department.” in their name.

basic merge in Power BI step 8

If you untick this box, the resulting column names will be like this

basic merge in Power BI step final

Watch out for duplicates!

One thing that threw me off when I first got into merge, was that sometimes the number of rows increases when you expand your merged table. For example, before the expansion, there were 290 rows and after it, there were 296.

This happens when there are some duplicates in the table that you are merging. For example, if we merge the employee table with the department table, some employees may have more than 1 department. Maybe those are the employees that were in one department first and then they moved to another department.

Therefore, I recommend you always make sure that the column you are selecting for merge in the second table contains unique values. If you notice that it contains some duplicates, remove them before the merge and this should fix this problem.

This was the most basic demonstration of merge. Let’s now look into some more advanced applications.

See also  Vidi Corp Announces Strategic Partnership with DesignRush to Offer Power BI Services Globally

Merging on Multiple Columns

Merging the 2 tables based on one column in each table is not your only option! You can also do the merge based on multiple columns in each table!
merging multiple columns in Power BI step 1

I have created an excel file with the following columns. I want to use columns Territory ID, StartDate and EndDate to bring the Name column into my SalesTerritoryHistory table

merging multiple columns in Power BI step 2

All I need to do for this is

  1. Initiate the merge using the merge button
  2. Hold CTRL to select multiple columns

merging multiple columns in Power BI step 3

As a result, I will get my “Name” column into the dataset

merging multiple columns in Power BI step

Merge with Aggregate

You must have noticed the option to aggregate values instead of expanding values in the example above

merge with aggregate in Power BI step 1

I am going to demonstrate what the aggregate function does use a different table called Sales_SalesPerson. In this table we have an ID of a salesperson, their sales quota and the commission that they pay.

merge with aggregate in Power BI step 2

When we expand the SalesOrderHeader Table, we can switch from “Expand” to “Aggregate”

merge with aggregate in Power BI step 3
At this point, I can select which columns I want to aggregate

merge with aggregate in Power BI step 4

I can also select the type of aggregation that I need by clicking on the downward arrow

merge with aggregate in Power BI step 5

In this example, I will select the count of customers they sold to and the sum of value for their sales.

merge with aggregate in Power BI step 6
After I click OK, I can see those additional columns in my data now

merge with aggregate in Power BI step final

Power BI Append

Basic Append

Append is used when you have 2 datasets with the same structure and you want to combine them together into 1. Take a look at the example below.

We have a table for sales of SalesPersonID = 279

basic append in Power BI step 1

We also have a similar table for sales of SalesPersonID = 274

basic append in Power BI step 2

You can combine them together by pressing the append.

basic append in Power BI step 3

After you press it, the append menu will ask you to select the second table for the append.

basic append in Power BI step 4

You need to select the second table and click OK. Afterwards, you will see a new step appearing in the applied steps menu.

See also  How to Connect Jira to Tableau

basic append in Power BI step 5

You will also see that our dataset now contains data for both salespeople!

basic append in Power BI step 6

Watch out for differences in column names!

Let’s take the 2 datasets below as an example. The dataset with SalesPersonID = 279 has a column “SalesPersonID”.

basic append in Power BI step 7

The dataset with SalesPersonID = 276 has a column “SalesPerson”.

basic append in Power BI step 8

If we append the 2 datasets together, the result will be as below. There will be 2 columns “SalesPersonID” and “SalesPerson”. Both of them will contain 50% of blank values.

basic append in Power BI step final

To avoid this behavior, make sure your column names are consistent. Go to your table with SalesPersonID = 276 and rename the column “SalesPerson” to “SalesPersonID”. This would fix this issue.

Append Three or More Tables

If we go into the original Order Header – 279 tables and press “append”, we will be able to switch to “Three or more tables”. At this stage, we will be able to select the other tables to append on the left. Press “Add” to add those tables and then you will see them appearing on the right.

append three or more tables in Power BI step 1

After clicking OK, you will see that the tables were successfully appended and you can now see all the SalesPersonIDs in a single table.

append three or more tables in Power BI step 2

Append as New

You also have an option of appending queries as new.

append vs new in Power BI step 1

Selecting this option gives you the same menu as before.

append vs new in Power BI step 2

However, after completing your append, you will find a new query called “Append1” appearing in your Power Query.

append vs new in Power BI step final

This table will contain the appended data from all the tables that you choose to append.

Power BI Append vs Merge Summary

In conclusion, I want to reiterate that the merge function is used to add new columns to your dataset whereas append is used to add new rows.

I hope you find this article useful! Have fun appending and merging!

Microsoft Power Platform

Everything you Need to Know

Of the endless possible ways to try and maximise the value of your data, only one is the very best. We’ll show you exactly what it looks like.

To discuss your project and the many ways we can help bring your data to life please contact:

Call

+44 7846 623693

eugene.lebedev@vidi-corp.com

Or complete the form below