Append vs. Merge in Power BI and Power Query

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2017-01-05_21h17_33

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 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 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.

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.

2017-01-05_20h25_40

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 same number of columns for each query*. (for example col1, col2,…, col10 in first query, after appending with same columns in the second query will result into one query with single set of col1,col2, …, col10)

There is an exception for 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:

2017-01-05_20h22_35

and Students of course 2:

2017-01-05_20h23_54

To append these queries, Click on one of them and select Append Queries from the Combine section of Home tab in Query Editor

2017-01-05_20h27_06

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.

2017-01-05_20h28_57

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.2017-01-05_20h31_01

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 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.

2017-01-05_20h33_01

Result of Append as simple as that

2017-01-05_20h39_59

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 match?

Append requires columns to be exactly similar to work in 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 is based on matching rows, rather than columns. The output of Merge will be 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 of 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 example, let’s have a look at that in action;

In addition to tables in first example, consider that there is another table for Course’s details as below:

2017-01-05_20h53_30

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;

2017-01-05_20h37_05

Select Course Query first, and then Select Merge Queries (as New)

2017-01-05_20h55_27

Merging Queries require joining criteria. Joining criteria is field(s) in each source query that should be matched with each other to build the result query. In this example, I want to Merge Course query with Append1, based on Title of the course.

2017-01-05_20h58_07

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 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.

2017-01-05_21h01_52

Now click on Expand column icon, and expand the New Column to all underneath table structure

2017-01-05_21h03_04

Result will be a table including columns from both tables, and rows matching with each other.

2017-01-05_21h04_02

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 appears in matching criteria. First three rows are students of Math course, then two students for English course, and because there is no student for Physics course you will see null values for students columns.

2017-01-05_21h17_33

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.

2017-01-05_21h18_39

Explaining what each join type will do is totally different post which I will write later. For now this picture explains it very well:

joins

Picture referenced from: http://www.udel.edu/evelyn/SQL-Class2/SQLclass2_Join.html

Download

Download the Power BI file of demo from here:


Enter Your Email to download the file (required)

Save

Save

Save

Save

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">