Power BI Desktop : Merge Query Options

No comments

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s