In Power BI Desktop, there are multiple options available when using Merge Query feature. This tutorial reviews each of the options.
Data for the tutorial can be obtained by downloading Excel file from here .
The file has Product sheet which has details of all the products my company sells
The file also has a Manufacturer sheet. This lists the Manufacturer and the products they make
Get Data
Open Power BI Desktop
From the ribbon select Get Data
Select Excel and click Connect
Browse to the location of the downloaded source file, select the file and click Open
Navigator window opens, select both Product and Manufacturer and click Edit
Query editor window opens
Check to make sure both the queries have the right header. If it does not, use First Row as Headers to set it
Get All the Products that my company sells and any Matching Manufacturers
Right click on Product query and select Duplicate to create a copy of the query. Rename the query to Left Outer Product
With Left Outer Product selected, from the ribbon select Merge Queries
In the Merge dialogue, select Manufacturer from the drop down
Highlight Product column in both the top and bottom tables
Leave Join Kind drop down to Left Outer (all from first, matching from second)
Click OK
Notice New Column is added to Left Outer Product query
Click on the double arrow next to New Column. This will display a select listing the columns from Manufacturer table
Uncheck Product and Use original column name as prefix
Click OK
Notice the query now displays all the Products sold by my company merged with Manufacturer details when available. If Manufacturer detail is not available, it’s replaced with null
Get All the Manufacturers and the Products they make with any matching Products sold by my company
Right click on Product query and select Duplicate to create a copy of the query. Rename the query to Right Outer Product
With Right Outer Product selected, from the ribbon select Merge Queries
In the Merge dialogue, select Manufacturer from the drop down
Highlight Product column in both the top and bottom tables
From Join Kind drop down select Right Outer (all from second, matching from first)
Click OK
Expand the New Column – similar to the steps mentioned earlier
Notice the query now displays all the Products that each of the Manufacturer makes merged with the Products sold by my company. If my company does not sell a Product, there is a null value
Get All the Products
Right click on Product query and select Duplicate to create a copy of the query. Rename the query to Full Outer Product
With Full Outer Product selected, from the ribbon select Merge Queries
In the Merge dialogue, select Manufacturer from the drop down
Highlight Product column in both the top and bottom tables
From Join Kind drop down select Full Outer (all rows from both)
Click OK
Expand the New Column – similar to the steps mentioned earlier
Notice the query now displays all the Products from both Product and Manufacturer merging where ever matches are found
Get Only Products that my company sells and has a related Manufacturer
Right click on Product query and select Duplicate to create a copy of the query. Rename the query to Inner Product
With Inner Product selected, from the ribbon select Merge Queries
In the Merge dialogue, select Manufacturer from the drop down
Highlight Product column in both the top and bottom tables
From Join Kind drop down select Inner (only matching rows)
Click OK
Expand the New Column – similar to the steps mentioned earlier
Notice the query now displays only Products that have a matching Manufacturer. There are no null values
Get Products that my company sells that does not have a matching Manufacturer
Right click on Product query and select Duplicate to create a copy of the query. Rename the query to Left Anti Product
With Left Anti Product selected, from the ribbon select Merge Queries
In the Merge dialogue, select Manufacturer from the drop down
Highlight Product column in both the top and bottom tables
From Join Kind drop down select Left Anti (rows only in first)
Click OK
Expand the New Column – similar to the steps mentioned earlier
Notice the query now displays Products sold by my company which do not have a matching Manufacturer
Get Products that my company does not sell
Right click on Product query and select Duplicate to create a copy of the query. Rename the query to Right Anti Product
With Right Anti Product selected, from the ribbon select Merge Queries
In the Merge dialogue, select Manufacturer from the drop down
Highlight Product column in both the top and bottom tables
From Join Kind drop down select Right Anti (rows only in second)
Click OK
Expand the New Column – similar to the steps mentioned earlier
Notice the query now displays Products that are not sold by my company
business intelligist 