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

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

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

Join Types

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

Video:

Download

Download the Power BI file of the demo from here:

Save

Save

Save

Save

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.