Due to time constraint we could not discuss merge options in detail in our last Power BI training in Sydney. So we decided to dedicate our weekly blog to this topic and ensure complete learning promised in our Power BI trainings. Merging Queries allows you to merge data sets that contain a common column. Merge query adds columns to the table or creates a new query containing the result. You must choose the right merge to get accurate results.
Power BI provides you with six types of merging options:
- Left Outer.
- Right Outer.
- Full Outer.
- Inner.
- Left Anti.
- Right Anti.
In this blog post, the six different kind of merge options in Power BI have been explained with examples. The data set chosen for this blog is simplified to ensure complete understanding of our readers. The data set consists of Sales table and a Products Table. Each of these tables contains a Product ID column which is used for merging.
The sales table consists of two columns, Product ID (used for merging) and the Order Quantity. Notice that the Products present in the sales table have Product ID from 1 to 6.
The second table in the data model is the Product table. It has 2 columns Product ID(used for merging) and the Product Cost. Notice that the products 7,10 and 11 donot exist in Sales Table.
Now that we are clear about the structure and data. Lets open the Query Editor.
1.Click on the Home Tab in the Ribbon.
2.Click on Transform data > Transform data.
Query editor opens having two quieries in the Queries section.
Let’s see what does this means.
Product ID for Sales table are: 1,2,3,4,5,6
Product ID for Product table are: 2,3,6,7,10,11.
Now, if we initiate the merge on Sales table then the Sales table acts like the left table and the Product table acts like the right table. Therefore, the left outer merge will contain everything in the Sales table. i.e., the Product IDs 1,2,3,4,5,6 must be present in the result.
To Merge Queries:
1.Click on Sales.
2.Click on Sales Table. Click on Home Tab in the Ribbon Menu.
3.Click on Merge in the Combine section.
4.Click on Merge as New.
A pop-up menu appears.
5.From the first drop-down menu, select Sales and click on Product ID
6.From the second drop-down menu, select Products and click on Product ID
A pop-up menu appears.
5.From the first drop-down menu, select Sales and click on Product ID
6.From the second drop-down menu, select Products and click on Product ID
7.Click OK.
8.Open Merge Query and click on the double headed arrow.
A pop-menu appears which allows you to choose column of your choice.
7.Click OK.
8.Open Merge Query and click on the double headed arrow.
A pop-menu appears which allows you to choose column of your choice.
9.Click OK.
Notice that all the Product ID from the Sales table are present in the resultant query but only the corresponding Product ID from Product table are present. For the products that are present in Sales table and not Product table, null value has been assigned.
Right Outer
This type of merger creates a merge of all rows from the second table and the corresponding rows from the first table.
Let’s see what does this means.
Product ID for Sales table are: 1,2,3,4,5,6
Product ID for Product table are: 2,3,6,7,10,11.
Now, if we initiate the merge on Sales table then the Sales table acts like the left table and the Product table acts like the right table. Therefore, the right outer merge will contain everything in the Product table. i.e., the Product IDs 2,3,6,7,10,11 must be present in the result.
To Merge Queries:
1.Click on Sales.
2.Click on Sales Table. Click on Home Tab in the Ribbon Menu.
3.Click on Merge in the Combine section.
4.Click on Merge as New.
A pop-up menu appears.
5.From the first drop-down menu, select Sales and click on Product ID
6.From the second drop-down menu, select Products and click on Product ID
7.Click OK.
8.Open Merge Query and click on the double headed arrow.
A pop-menu appears which allows you to choose column of your choice.
9.Click OK.
Notice that all the Product ID from the Products table are present in the resultant query but only the corresponding Product ID from Sales table are present. For the products that are present in Product table and not Sales table, null value has been assigned
Full Outer
Full Outer Merge creates a set resulting from all the rows from both the tables. Null values are substituted in place of non-existing values.
Let’s see what does this means.
Product ID for Sales table are: 1,2,3,4,5,6
Product ID for Product table are: 2,3,6,7,10,11.
Now, if we initiate the merge on Sales table then the Sales table acts like the left table and the Product table acts like the right table. Therefore, the full outer merge will contain everything in the Sales table and the Product table, i.e., the Product IDs 1,2,3,4,5,6,7,10,11 must be present in the result.
To Merge Queries:
1.Click on Sales.
2.Click on Sales Table. Click on Home Tab in the Ribbon Menu.
3.Click on Merge in the Combine section.
4.Click on Merge as New.
A pop-up menu appears.
5.From the first drop-down menu, select Sales and click on Product ID
6.From the second drop-down menu, select Products and click on Product ID
7.Click OK.
8.Open Merge Query and click on the double headed arrow.
A pop-menu appears which allows you to choose column of your choice.
9.Click OK.
Let’s see what does this means.
Product ID for Sales table are: 1,2,3,4,5,6
Product ID for Product table are: 2,3,6,7,10,11.
Now, if we initiate the merge on Sales table then the Sales table acts like the left table and the Product table acts like the right table. Therefore, the inner merge will contain everything in common between the Sales table and the Product table, i.e., the Product IDs 2,3,6 must be present in the result.
To Merge Queries:
1.Click on Sales.
2.Click on Sales Table. Click on Home Tab in the Ribbon Menu.
3.Click on Merge in the Combine section.
4.Click on Merge as New.
A pop-up menu appears.
5.From the first drop-down menu, select Sales and click on Product ID.
6.From the second drop-down menu, select Products and click on Product ID.
7.Click OK.
8.Open Merge Query and click on the double headed arrow.
A pop-menu appears which allows you to choose column of your choice.
9.Click OK.
Let’s see what does this means.
Product ID for Sales table are: 1,2,3,4,5,6
Product ID for Product table are: 2,3,6,7,10,11.
Now, if we initiate the merge on Sales table then the Sales table acts like the left table and the Product table acts like the right table. Therefore, the right anti merge will contain the Product IDs present in the Product table but not the Sales Table, i.e., the Product IDs 7,10,11 must be present in the result.
To Merge Queries:
1.Click on Sales.
2.Click on Sales Table. Click on Home Tab in the Ribbon Menu.
3.Click on Merge in the Combine section.
4.Click on Merge as New.
A pop-up menu appears.
5.From the first drop-down menu, select Sales and click on Product ID.
6.From the second drop-down menu, select Products and click on Product ID.
7.Click OK.
8.Open Merge Query and click on the double headed arrow.
A pop-menu appears which allows you to choose column of your choice.
9.Click OK.
Let’s see what does this means.
Product ID for Sales table are: 1,2,3,4,5,6
Product ID for Product table are: 2,3,6,7,10,11.
Now, if we initiate the merge on Sales table then the Sales table acts like the left table and the Product table acts like the right table. Therefore, the right anti merge will contain the Product IDs present in the Sales table but not the Product Table, i.e., the Product IDs 1,4,5 must be present in the result.
To Merge Queries:
1.Click on Sales.
2.Click on Sales Table. Click on Home Tab in the Ribbon Menu.
3.Click on Merge in the Combine section.
4.Click on Merge as New.
A pop-up menu appears.
5.From the first drop-down menu, select Sales and click on Product ID.
6.From the second drop-down menu, select Products and click on Product ID.
7.Click OK.
8.Open Merge Query and click on the double headed arrow.
A pop-menu appears which allows you to choose column of your choice.
9.Click OK.
Notice that all the Product ID that are not present in the Product table but are there in the Sale table are present in the resultant query. Null values have been assigned for these Product IDs in the Product table.
Conclusion:
Merging data sets in Power BI means to combine data sets that contain a common column. Power BI provides six different merging options to its users in Power Query Editor. This blog post describes the six merging options using Venn diagram, intuitive reasoning and practical examples.
Are you a data analyst and want to learn more about Power BI?
Why not sign up for Power BI training in Australia. We provide our services in the following regions:
