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