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 “Full Outer Join”, 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 “Full Outer” join type the final output table will be as follows:
As a result, we will get all rows from both the tables. The rows highlighted in red box are rows from Table 2 that could not find a match in table 1. The rows highlighted in yellow box are rows from Table 1 that could not find a match in Table 2. The rows that are not highlighted are the rows which were common to both the tables i.e. Table 1 was able to fetch the codes from Table 2.
(Full outer is a combination of results of left outer and right outer joins)
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