Select Distinct Logo Clear Background

Business Analytics Blog

APPEND data in Power Query

Combine data with APPEND in Power Query

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

UNION in SQL – Select Distinct

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

 

A sample sales dataset

 

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

 

A sample sales dataset

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

Two similar data tables in Power BI before we UNION then

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

Select the first table in table view in Power BI

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

Right click the edit query option on the data pane

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

Power Query editor window

 

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’

Append Queries in Power BI

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

Append tables in Power Query, selecting the tables

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

Appended table in Power Query

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

appended table with an unequal number of columns

 

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

Appended table in Power Query with mismatched column names

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

Remove duplicates in Power Query

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

Appended Query in Power query after removing duplicates

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

Subscribe to our channel to see more SQL tips and timesavers

Select Distinct YouTube Channel

 

Or find other useful SQL, Power BI or other business analytics timesavers in our Blog

Our Business Analytics Timesavers are selected from our day to day analytics consultancy work and are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people. Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!

Business Analytics Blog

By Simon Harrison

Simon Harrison Founder of Select Distinct Limited and a business intelligence expert