Merge Queries in Power BI allows you to join two existing tables together based on matching values from one or multiple columns.
Basically, it is equivalent to JOINS which combines two queries into single queries.
You can use different types of joins to merge the tables to get the desired result as per you requirement.
Lets try to understand the requirement of using Merge with the help of following example.
As you can see in below screenshot, there are two tables named Customer and Department.
Customer table:Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
Department table:
Tables Customers contains the basic details of customers while Department table contains the department details.
Lets assume that you want to combine or merge the department details with Customer table, or create a separate new merge table that contains the customer details along with required department details. It can be done using Merge Queries.
Lets see how to use Merge queries to combine the two tables into single table.
Merge using a Merge Queries
Lets, use the Merge Queries to combine the Department Name and DeptCode column from Department table with Customer table.
When you select Merge Queries, It opens the the Merge dialog box, with the selected query as the left table of the merge operation.
Go to Power Query Editor, then select the customer table that will be considered as a left table in Merge operation as you want to combine the columns from Department table into Customer table.
After that under Home tab, in the Combine group. Select Merge Queries from drop down list box.
Once you select Merge Queries, a Merge dialog box opens.
You can see there is a preselected Customer table, that will be a left table for merge.
Lets provide the required details as follow:
First select the Department table from dropdown that will be a Right table for merge.
After that join the Customer and Department tables by using the DeptId column from both tables, for that select the DeptId column from each table.
Note that, You can also select multiple columns to performing the join by selecting Ctrl as you select the columns.
Next select a type of join that you want to apply between these tables from Join Kind dropdown box.
Once you done with this, a message appears with an estimated number of matches at the bottom of the dialog box.
Next click on OK button.
Note that, the column headers don’t need to match between tables for using a joins between tables. But columns must be of the same data type otherwise the merge operation might not produce the correct results.
You can see, a new table column is created in Customer table named as Department and it holds the values corresponding to the right table (Department table) on a row-by-row basis.
Now click on expand column icon, you will get the lists of Department’s table columns.
After that, tick on expand and select the columns that you want to merge in Customer table.
After that click on Ok button.
Once you click on OK button, you will see the Department Name and DeptCode column is added into Customer table.
Now the customer table contains all the department details of each customer.
After that save you changes, click on Close & Apply icon.
Merge using a Merge as new
When you select Merge queries as new, it opens the Merge dialog box without any preselected tables for the merge operation also the output will be a new table including columns from both tables based on specified Join.
Go to Power Query Editor, under Home tab, in the Combine group. Select Merge queries as new from drop down list box.
Once you select Merge Queries as New, a Merge dialog box opens as shown below.
Lets provide the required details as follows:
Select the Left table and Right table for merge as follows:
- Left table for merge: The first selection, from top to bottom of your screen.
- Right table for merge: The second selection, from top to bottom of your screen.
Lets select the Customer table that will be a Left table for merge and Right table Department table that will be a Right table for merge.
Once you click on OK button, you will see a new table named Merge1 is created in Data model as shown below.
Lets expand the Department column. Click on Expand column icon.
Once you click on OK button, you will see the Department Name and DeptCode columns are added to new table, also you can change the table name.
Lets give it name as CustomerDept.
And finally save your changes by clicking on Close & Apply icon.
Also Read..
Group By the data using Power Query
Create Index column using Power Query
