Combining two queries in Power Query or in Power BI is one of the most basic and also essential tasks that you would need to do in most of data preparation scenarios. There are two types of combining queries; Merge, and Append. Database developers easily understand the difference, but the majority of Power BI users are not developers. In this post, I’ll explain the difference between Merge and Append, and situations that you should use each. If you want to learn more about Power BI, read Power BI online book, from Rookie to Rock Star.
Why Combine Queries?
This might be the first question comes into your mind; Why should I combine queries? The answer is that; You can do most of the things you want in a single query, however, it will be very complicated with hundreds of steps very quickly. On the other hand, your queries might be used in different places. For example one of them might be used as a table in Power BI model, and also playing the part of data preparation for another query. Combining queries is a big help in writing better and simpler queries. I’ll show you some examples of combining queries.
The result of a combine operation on one or more queries will be only one query. You can find Append or Merge in the Combine Queries section of the Query Editor in Power BI or in Excel.
Append means results of two (or more) queries (which are tables themselves) will be combined into one query in this way:
- Rows will be appended after each other. (for example appending a query with 50 rows with another query with 100 rows, will return a result set of 150 rows)
- Columns will be the same number of columns for each query*. (for example, col1, col2,…, col10 in the first query, after appending with same columns in the second query will result into one query with a single set of col1,col2, …, col10)
There is an exception for the number of columns which I’ll talk about it later. Let’s first look at what Append looks like in action;
Consider two sample data sets; one for students of each course, Students of course 1:
and Students of course 2:
To append these queries, Click on one of them and select Append Queries from the Combine section of Home tab in Query Editor
If you want to keep the existing query result as it is and create a new query with the appended result choose Append Queries as New, otherwise just select Append Queries. In this example, I’ll do Append Queries as New, because I want to keep existing queries intact.
You can choose what is the primary table (normally this is the query that you have selected before clicking on Append Queries), and the table to append.
You can also choose to append Three or more tables and add tables to the list as you wish. For this example I have only two tables, so I’ll continue with the above configuration. Append Queries simply append rows after each other, and because column names are exactly similar in both queries, the result set will have same columns.
The result of Append as simple as that
Append is similar to UNION ALL in T-SQL.
What about Duplicates?
Append Queries will NOT remove duplicates. You have to use Group By or Remove Duplicate Rows to get rid of duplicates.
What if columns in source queries are not exactly matched?
Append requires columns to be exactly similar to work in the best condition. if columns in source queries are different, append still works, but will create one column in the output per each new column, if one of the sources doesn’t have that column the cell value of that column for those rows will be null.
Merge is another type of combining queries which are based on matching rows, rather than columns. The output of Merge will be a single query with;
- There should be joining or matching criteria between two queries. (for example StudentID column of both queries to be matched with each other)
- Number of rows will be dependent on matching criteria between queries
- Number of Columns will be dependent on what columns selected in the result set. (Merge will create a structured column as a result).
Understanding how Merge works might look a bit more complicated, but it will be very easy with an example, let’s have a look at that in action;
In addition to tables in the first example, consider that there is another table for Course’s details as below:
Now if I want to combine Course query with the Appended result of courseXstudents to see which students are part of which course with all details in each row, I need to use Merge Queries. Here is the appended result again;
Select Course Query first, and then Select Merge Queries (as New)
Merging Queries require joining criteria. Joining criteria is field(s) in each source query that should be matched with each other to build the resulting query. In this example, I want to Merge Course query with Append1, based on Title of the course.
I’ll talk about types of join later. For now, continue the selection, and you will see these two queries match with each other based on the Course title, result query will be same as the first query (Course in this example), plus one additional column named as NewColumn with a table in each cell. This is a structured column which can be expanded into underlying tables. if you click on an empty area of the cell containing one of these tables, you will see the sub table underneath.
Now click on Expand column icon, and expand the New Column to all underneath table structure
The result will be a table including columns from both tables, and rows matching with each other.
Columns in the left-hand side are coming from Course table, columns in the right-hand side are coming from Students table. Values in the rows only appear in matching criteria. First three rows are students of Math course, then two students for the English course, and because there is no student for Physics course you will see null values for students columns.
Merge is similar to JOIN in T-SQL
There are 6 types of joins supported in Power BI as below, depends on the effect on the result set based on matching rows, each of these types works differently.
Explaining what each join type will do is a totally different post which I wrote about it here. For now, this picture explains it very well:
Picture referenced from http://www.udel.edu/evelyn/SQL-Class2/SQLclass2_Join.html
Download the Power BI file of the demo from here:
21 thoughts on “Append vs. Merge in Power BI and Power Query”
Thank you so much for this post. It helped me understand both merge and append a bit clearer.
This is wonderful. Thank you so much for the post. This demonstrates clearly the difference of merging and appending 2 tables.
Great post! Thank you!
Very helpful. Thank you very much.
Probably the easiest and simple explanation between append and merge for Power BI I online.
This is very helpful. I have a question relates to Append Multiple Tables. The tables to be appended had New Columns created by LOOKUPVALUE from a separate table. Each individual tables’ lookupvalue function all worked well. ( returned values to added columns). However, after append these tables ( with added columns) together, the added columns did not appear. Added Columns completely dropped after Append Queries function.
Can you please assist to understand how to solve this issue?
LookupValue is a function in DAX. this blog post that I wrote and the whole functionality explained here is about Power Query. Power Query transformation happens before loading data into Power BI. however, DAX expressions evaluate AFTER data loads into Power BI. When you do append in the Power Query, there is no LookupValue there to give you the output you want. If you want to achieve the same output, you have to use the alternative method to lookupvalue function but in Power Query way, called Merge.
After I append 2 tables, the result creates a duplicate rows from Table 1. What is the issue in this case?
Append will not remove duplicates! You have to remove duplicates yourself afterward. and this article explains some tips to get it working properly.
I have Query1 with some applied steps,
then I create new Query2,
Now I want to append the Query2 to the Query1, and also want the applied steps of Query1 to be applied to Query2 when it is appended.
Is this possible ?
If you want some same steps to be applied to both queries, you should create a custom function.
Is it possible to remove or delete old tables after I merged them into one?
You cannot remove or delete the table.
However, you can (and most of the time SHOULD) disable the “Enable Load” feature of that table to save memory and avoid the unnecessary table to be loaded into Power BI
If you have two different table structures and you have some matching columns from both tables and few new columns from table two and you only want to display distinct columns from each table then what is the best way to do this?
Do you have a screenshot of the data in your tables and what you want to achieve?
Will data in a merged query refresh every time I refresh the data?
I mean say I merge table A and B today and get the merged query C (table A and B are live).
Tomorrow when I refresh query C, table A and B, will query C have the latest data from table A and B?
Yes, refreshing the merged query will trigger the refresh of underlying queries.
however, usually, we do refresh of everything at once in Power BI, not table by table.
very helpful, thanks.
Thanks for the article. One thing that is not clear to me is how Power BI decides which columns to append – does it use Column Name, position, or both?
For example, if I have table A with columns Employee Name and Company, and a table B with columns Company and Employee Name, how will these two tables be appended?
Append is based on the NAME of the columns. and Power Query is case sensitive. if you have table1 with columns A and B, and then table2 with columns B and A. the append would be appending values correctly.
Very good and simple explaination. Thanks Reza.