Merge Two Tables In Power BI

Introduction

 
In my previous article:
I have shown you how to import a table from a SQL server into Power BI. Today in this article I am going to show you how to import two tables from a SQL Server and join them using Power BI and create a new table.
 
Also, you will learn how to create a report from this table. So, let’s start with importing a table from SQL Server into Power BI Desktop
 
I already shared the path of downloading the dummy database in my previous article Import Data From SQL Server Into Power BI. You can download the “AdventureWorks2014.bak” from that path.
 
Step 1
 
Your first step should be connecting your database with power bi desktop (as per previous article). If you have already connected with the database then go to ‘Recent sources’ and click on your SQL source.
 
Joins Of Two Tables In Power BI
 
Your connection will be shown like the below image.
 
Joins Of Two Tables In Power BI
 
Step 2
 
Now double click on your database, you will be able to see the tables. Select the tables ‘Person.AddressType’ and ‘Person.BusinessEntityAddress’.This time we will use ‘Transform Data’ instead of ‘Load’.
 
Joins Of Two Tables In Power BI
 
After clicking on ‘Transform Data’, a ‘Connection settings’ pop-up will be shown like below image. Check ‘Import’ and then click ‘Ok’.
 
Joins Of Two Tables In Power BI
 
Your connection is done and the ‘Power Query Editor’ window will be opened.
 
Joins Of Two Tables In Power BI
 
Step 3
 
Now select the first table and click on ‘Combine’ at the top of left side of the tool. Here you get two options, ‘Merge Queries’ and ‘Append Queries’.
 
Joins Of Two Tables In Power BI
 
Click on ‘Merge Queries’, you will again get two options ‘Merge Queries’ and ‘Merge Queries as New’.
 
Merge Queries
 
This option is used to merge two table together and does not create a new table.
 
Merge Queries as New
 
This option is required to merge two or more table and create a new one. You need to click on ‘Merge Queries as New’ to create a new one.
 
Joins Of Two Tables In Power BI
 
In the first marked list box you need to select another table.
 
Joins Of Two Tables In Power BI
 
Step 4
 
Now select the key column or the joining condition of these two tables; i.e. ‘AddressTypeId’ and click ‘Ok’.
 
Joins Of Two Tables In Power BI
 
Now you will get the new table name as ‘Merge 1’ and you can see the last two columns have the same name. You can remove the duplicate column by right clicking on that column and clicking on ‘Remove’.
 
Joins Of Two Tables In Power BI
 
You can also rename the Merge table by right clicking on the table and clicking on ‘Rename’. I renamed my table as ‘BusinessAddress’.
 
Joins Of Two Tables In Power BI
 
Step 5
 
To fill the data into the last column click the check box and select the column to insert. I selected ‘Person.address’. It will call ‘Person.Address’ table and show all columns of this table.
 
Joins Of Two Tables In Power BI
 
Joins Of Two Tables In Power BI
 
Select the column that you need and also uncheck the below box, ‘Use original column name as prefix’. Click ‘Ok’.
 
Joins Of Two Tables In Power BI
 
City column has successfully inserted into the merge table.
 
Click on Apply to save changes.
 
Joins Of Two Tables In Power BI
 

Conclusion

 
I hope this will help you to understand how to merge tables in Power BI. In mynext article I will use this table to create a report. So, stay with us and wait for the next article. Thanks for reading.