Table of Contents
Example
- “Code” column is missing in table 1.
- “Country” and “Parts” are common columns in both the tables.
- Some of the countries and parts do not have code in Table 2 (India-Item1, UAE-Item2)
- Some of the countries and parts have code in Table 2 but do not have quantity in Table 1 (India-Item2, Belgium-Item1)
Steps
- The first thing we need in order to perform merge query is to import the tables into power query by executing the below steps:
- Click on any cell in table 1
- Data tab->from table
- Home tab->Close & Load-> Close & Load
- Repeat the steps for table2
- Open the table 1 query by double clicking on the query name in the “Workbook Queries” pane.
- Home tab->Merge Queries->Merge Queries as new
- Merge query dialog box will appear, we will perform merge query by selecting common columns and the join kind. After selecting the common columns and join type as “Right Outer”, click on ok.
- A new query named as “Merge 1” will with additional column named as Table2 containing tables in each row will appear.
- Click on the double arrow icon and a list of columns from the other table will appear
Result
Using the “Left Anti” join type the final output table will be as follows:
As a result, only those rows of Table 1 which could not find a match in Table 2 are appearing in the output table.
Important:
Please note that Power query is case sensitive. The common columns used for merge query should contain data in the same case. For instance, If Table 1 has country as “India”, Table 2 should also have Country name as “India”. Any variation in the case “INDIA”, “india” will give output as null/no match. If the cases are not the same one must use, Right click Transform > Upper/Lower case feature to make the case consistent before using merge query feature