Business Analytics Blog

How to Append Data in Power Query: A Step-by-Step Guide

Last week we looked at how to combine datasets in SQL using UNION

This week, we are looking at using the same source data, but doing the combining in Power Query, via the transform options in Power BI

It is not always possible to combine the data in SQL, particularly if the data is coming from different systems

So the next best option is to combine the data on the way into Power BI using Power Query

We will use two similar sales data sets, the first covers store numbers 1 to 5 (SalesA)

We have a second dataset, covering store numbers 5 to 10 (SalesB)

Now these could be two entirely different companies, both of which have a matching store ID (Store_ID 5)

Using APPEND to Combine Data from Multiple Tables

Firstly we will bring in the two separate data tables

In our case we are using a SQL server database, but your data sources could be a mixture from different systems that you cannot alter

Go to the table view in Power BI desktop and select the first table

Right click ‘Edit query’ option on the data pane for the first table

The interface now opens up a Power Query editor window and we can see the data in the first table

Combine the tables using the APPEND QUERIES option

On the Home tab, under the Append Queries button, select the drop down and choose ‘Append Queries as New’

Now select the two tables in the Append dialogue box that pops up

Click OK, and we now see the new table created, with all 10 records.
Please note this method does not eliminate duplicates at this stage

Here is the new table after the data is appended

From here you can rename the table, apply any other transformations you may need and use it as you would any other table

If the columns are not identical what happens?

To demonstrate this, go back to Sales A and add a column

We added a custom column called Source and populated it with “SalesA”

if we now look back at the Appended table, the extra column is automatically added, and the SalesB data is shown as NULL values in this new column

What happens if the columns have different names?

Go into SalesB and add a custom column called ‘Company and populate it with “SalesB”

Now, if we look back at the appended table, another new column is added, with NULL values in the “SalesA “data

You can see that if column names dont exactly match then the append will treat them as separate columns

To correct it go back into one of the source tables and change the field name to match

How to remove duplicates in append queries?

You may actually need to remove duplicates

To do this, select all of the columns you want to be included in your duplicate checking. Note that we have only selected the first three columns, as the Source Column would have two different values for Store ID 5

Then select ‘Remove Rows’ on the Home Ribbon, and select Remove Duplicates from the drop-down options

The duplicated data is now excluded and we can see 9 rows

The key things to remember when appending data in Power Query is to make sure that the field names and data types are the same before running the append

It does not matter if the columns are in a different order as the final column order is derived from the first table

You can append more than two tables, which is a very similar process

Append data in Power Query: Conclusion

You can use this method when you don’t have the option to combine the data prior to loading it, for example it is coming in from different systems

It is fairly self explanatory and easy to follow but very powerful, just be careful with the field names and data types. If you make a mistake go back to the underlying tables and make the field match and the append will be correct