How to Merge Two Queries or Tables in Power BI

Merging Two Queries or Tables in PowerBI

Merge is a Technique of Combining Two Queries or Tables in Power BI. We can Merge Two Tables based on the Key Column of both the Tables.

Merge will works in the same way as SQL Joins.

Scenario :

Suppose we have tables which we want to Merge as follows..

LT_CustByRegion ( Left Table )

RT_CustBySegment ( Right Table )

Now we will Merge, these Two Tables into a New Table using different Joins based on the Key Colum "CustId", as shown below.

1)Go to Query Editor > Home > Merge Queries > Merge Queries as New

Here we are selecting the "Merge Queries as New" , as we are merging Queries into a New Table.

2) Next Select the Merge Join Kind (Left Join or Right Join or Inner Join or Full Outer Join or Left Anti Join or Right Anti Join )

A) Merge Join Kind : Left Outer

Here we are using the Left Outer Join , which will return the all rows from Left Table and matching rows from Right Table.

First we should select the Common Key Column from both the Tables and then we have to select the Join Kind.

Now the Merge Left output Table is generated where the Right Table output is added in a New Column at the last.

Now We need to expand the Right Table and select the required Columns from Right Table that we want in Merge Left output.

Here I am selecting only the "CustSegment" from Right Table into Merge Output

Result :

All the rows from Left Table and Matching rows from Right Table.

The non Matching values will shown as "null"

B) Merge Join Kind : Right Outer
When we select the Right Outer Join , it will return all rows from Right Table and matching rows from Left Table.
Result :

All the rows from Right Table and Matching rows from Left Table.

The non Matching values will shown as "null"

If we select all the Columns from Right Table, then result as follows..

C) Merge Join Kind : Inner Join
When we select the Inner Join , it will return only the matching rows from both the Tables.

Result :

The matching rows from both the Tables.

D) Merge Join Kind : Full Outer Join
When we select the Full Outer Join , it will return all the rows from both the Tables.
Result :

All the rows from both the Tables.

E) Merge Join Kind : Left Anti Join
When we select the Left Anti Join , it will return only the rows from Left Table which does not have the matches in Right Table.
Result :

The only rows from rows from Left Table which does not have the matches in Right Table.

F) Merge Join Kind : Right Anti Join

When we select the Right Anti Join , it will return only the rows from Right Table which does not have the matches in Left Table.
Result :

The only rows from rows from Right Table which does not have the matches in Left Table.

-------------------------------------------------------------------------------------------------------- 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------