Excel – Types of Merging of Queries in Power Query

In Power Query, we have an option to Combine two tables using “Merge queries”.  

• Merge Queries: Merge with current query with another query
• Merge Queries as New: Merge with current query with another query and create New query

In this article, we explore different types of merging of queries in Power Query with two tables. We will combine two years of sales data (2020 and 2021) with the key field “Cust_ID”.

Implementation:

Follow the below steps to implement merging of queries:

Step 1: Open Power Query Editor.

Step 2: Select your Query “Sales_2020”.  Go to Home >> Merge Queries >> Merge Queries as New.

We have six type of joins:

1. Left Outer (all from first, matching from second)
2. Right Outer (all from second, matching from first)
3. Full Outer (all rows from both)
4. Inner (Only Matching rows)
5. Left Anti (rows only in first)
6. Right Anti (rows only in second)

Step 3: Select Second table; Select key fields “Cust_ID” in both tables; Select Left Outer (all from first, matching from second) and Press “OK”.

Step 4: Above step create a new query “Merge 1” as below in your power query.

Note: Sales_2021 is not a text value it is a table for each row.

Step 5: Click “Sales_2021” drop down icon; Make sure Expand is selected. Press “OK”.

Now merged table with all columns.

Step 6: Change query name “Left Outer”.

Step 7: Press “Close & Load”, It will add a new sheet “Left Outer” with a Merge table.

Output:

• Left Outer [All records from Sales_2020 and matched records from Sales_2021]:

Select different types of joins in step 3 and generate respective merge tables

• Right Outer  [All records from Sales_2021 and matched records from Sales_2020]

• Full Outer  [All records from both Sales_2020 and Sales_2021]

• Inner  [Only matched records from both Sales_2020 and Sales_2021]

• Left Anti  [Un-matched records from Sales_2020]

• Right Anti [Un-matched records from Sales_2021]