In this blog we will learn how to append two or more table into new table or existing table. The Append Queries in Microsoft Power BI is an equivalent of UNION ALL in SQL.
Consider two sample customer table; one for CustomerOne:
Open Microsoft Power BI for Desktop > Get Data > Excel > the excel file. You should see this:
Select tables and click on Transform Data.
Now it’s time to proceed with the Append operation itself:
Click the little triangle on the main “Append Queries” button. You’ll get 2 options:
- Append Queries – this operation would add rows into an existing table
- Append Queries as New – this operation will create a new output table from 2 (or more) appended tables. I’m going for this option.
So, let’s see what happens after clicking the “Append Queries as New” button:
You either append 2 tables like me OR you can do “Three or more”. So, keep in mind you are NOT limited to 2 tables only.
One important thing to understand how the tables are actually “appended” together. Power BI looks at column names. If it finds the same columns like in my case (CustomerID, First Name, Last Name, Contact No) in both the tables, it won’t create any new columns and it will fit everything right into those 4 columns.
What about Duplicates?
Append Queries will NOT remove duplicates. You have to use Group by or Remove Duplicate Rows to get rid of duplicates.
So, you press OK and you get the result of your operation. Now the result:
Hope this helps!