Why do you Merge and Append data in Power BI?

Dec 14, 2023 | Power BI Hints and Tips

Merging and appending join tables together in very different ways.  Both are accessed from the Power Query Editor.  Depending on how data is supplied to you and what you may want to do with it will determine whether you need to use either of these options.  Merging creates a query that combines 2 tables resulting in more columns in the new table than there were in the original table while appending combines 1 or more tables resulting in more rows.  Each of these options are explained in more detail below.  Both are also available when using Power Query in Microsoft Excel.

Merge

Normally the relationships between the tables set up in the Model View are all you need to create visualisations containing fields from multiple tables.  Sometimes you may require a different connection for some visualisations.  This is where Merge is used.

  1. Click on the Transform Data icon on the Home Tab from Power BI Desktop.
  2. The Power Query Editor screen will now be open.
  3. From the Home tab select the drop down beside Merge Queries and select Merge Queries as new.  (this will leave the original queries unchanged and produce a new query containing the merged data).
Merge queries icon image

NOTE: If the Merge icon is not visible it could be due to the screen resolution and it may be found under the Combine icon.

4. The first table to be used is chosen in the top drop-down box and the other table in the second drop down box.

5. The fields that connect the tables together in each table must be selected.  In the example shown below the Company field form the Customers table is connected to the Customer field in the orders table.

Merge query dialog box image

6. The Join Kind is selected from the 6 options available.  The text in brackets explains what each type of Join Kind is.

7. Fuzzy matching is optional and allows data that is similar but not identical to be connected.  This should be used with caution as you may find that data that does not mean the same things gets connected producing incorrect results.

The merged query will now be available from the query editor window and also from Power BI Desktop once the query has been Closed and Applied.  It can be renamed from either location.

NOTE: Only 2 tables can be merged at a time.  If you need to merge a 3rd table you merge 2 as described above, take that result, then merge it with the 3rd.

Append

Appending is used to add tables below each other.  For example, if you are provided with the years data in 4 separate files, 1 for each quarter, or on 4 separate sheet tabs in Excel you would need to append these to produce an overall table for the year if you needed visualisations showing the yearly results.

For appending to work all the tables must have the same column headings but they don’t have to be in the same order.  If the column headings are inconsistent this must be dealt with before the append is performed.

  1. Click on the Transform Data icon on the Home Tab from Power BI Desktop.
  2. The Power Query Editor screen will now be open.
  3. From the Home tab select the drop down beside Append Queries and select Append Queries as new.  (this will leave the original queries unchanged and produce a new query containing the appended data).
Append queries icon image

NOTE: If the Append icon is not visible it could be due to the screen resolution and it may be found under the Combine icon.

4. If there are only 2 tables to append keep the option on Two tables and choose the 2 tables required in the 2 drop down boxes.

Append 2 tables query image

5. If more than 2 tables are required then click on 3 or more tables.

6. Add the tables required to the pane on the right and click OK.

Append 3 or more tables query image

The appended query will now be available from the query editor window and also from Power BI Desktop once the query has been Closed and Applied. It can be renamed from either location.

Further Reading

If you’ve enjoyed reading about why you would merge and append data in Power BI, there are some other blogs below that you might find useful:

Want to learn more about Microsoft Power BI? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Power BI Desktop Courses I run.