Append vs. Merge in Power BI and Power Query

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

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

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

2017-01-05_20h33_01

The 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 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:

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

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

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

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 a totally different post which I wrote about it here. For now, this picture explains it very well:

joins

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:

    Enter Your Email to download the file (required)

    Save

    Save

    Save

    Save

    Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
    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.

    21 thoughts on “Append vs. Merge in Power BI and Power Query

    • This is wonderful. Thank you so much for the post. This demonstrates clearly the difference of merging and appending 2 tables.

    • 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.
        Cheers
        Reza

    • Hi,
      After I append 2 tables, the result creates a duplicate rows from Table 1. What is the issue in this case?
      Thanks.

    • @Rad,

      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 ?

    • Hi Reza,
      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?

    • Hi Reza,

      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?

      Thanks
      Jessie

      • 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.
        Cheers
        Reza

    • Hi Reza,

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

    Leave a Reply