Merge Query is a powerful transformation tool in Power Query. It is equivalent to JOINS in SQL. Power Query supports six kinds of joins:
While the most used join kind is Left Join (equivalent to VLOOKUP in Excel), other join kinds are equally useful. This article explores three such use cases.
This article assumes that we know how to apply merge queries in Power Query. For more details, please refer to the article
Use Case 1: Getting only matched records in a table
We have two tables: Orders and Returns.
Download Sample Data file
Order table has all the orders(5497 records), and Returns has order status of 572 orders with status as Returned.
We need one table holding details of returned orders.
With Orders table selected in Power Query, perform Merge Queries as New on Order ID with Returns table, and select Right Outer Join:
The output table holds 572 records of all the orders which have returned.
Use Case 2: Getting non-matched records in a table
The reverse of the Use Case 1: we need a table holding all records for the status of the orders as not returned. This time, we repeat the steps performed earlier with one change: use Left Anti as join kind.
It excludes all the records from the Order table, which are matching from the Returned table.
Use Case 3: Comparing two tables to find differences
Consider the following tables:
The records highlighted in yellow are common in both the tables.
The goal is to find if there is a different Name assigned in the tblB for the same ID.
For this, we perform Merge Queries twice.
Step 1: Merge Queries as New on tblA
In the first step, we Merge queries to find records which are matching on both ID and Name with join kind as Left Anti:
Output: We have four such records that do not have an exact match with tblB on ID & Name.
Remove the column for tblB.
Step 2: Merge Query on ID only on the new table
Now we need the Name from tblB against the corresponding ID; hence we Merge query on ID with Left Outer Join:
Expand the tblB to get the corresponding Name:
In conclusion, there is only one record in tblB where ID is matching with tblA but has a different Name, whereas three records from tblA have no match in tblB.