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
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.
Your connection will be shown like the below image.
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’.
After clicking on ‘Transform Data’, a ‘Connection settings’ pop-up will be shown like below image. Check ‘Import’ and then click ‘Ok’.
Your connection is done and the ‘Power Query Editor’ window will be opened.
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’.
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.
In the first marked list box you need to select another table.
Step 4
Now select the key column or the joining condition of these two tables; i.e. ‘AddressTypeId’ and click ‘Ok’.
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’.
You can also rename the Merge table by right clicking on the table and clicking on ‘Rename’. I renamed my table as ‘BusinessAddress’.
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.
Select the column that you need and also uncheck the below box, ‘Use original column name as prefix’. Click ‘Ok’.
City column has successfully inserted into the merge table.
Click on Apply to save changes.
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.