What is Merge query? #
Merge query is a feature of power query which helps us to get data from multiple files based on one or multiple common columns. Merge query works in similar fashion as VLOOKUP in excel but we can do a lot more using this feature.
VLOOKUP in excel means Vertical lookup. The function searches for a lookup value in the left most column of a section in your spreadsheet called the table array.
In Merge query, we can select multiple columns in the same sequence in both the tables and bring all the required columns from one table into another in few clicks.
Merge query also allow us to retain only rows which are matching or not matching either in Table 1 or Table 2. It can be done with the help of joins which can be seen in the below image:
Merge Types #
Where to find Merge Query? #
Example #
Example file can be downloaded from here.
Let’s understand merge query and it’s join types with the following example:
Here we can see that
- 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)
Groundwork #
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, click on ok.
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.
Scenarios #
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.
Scenario 1 #
New table showing Code column from table 2 against the rows in table 1.
Using the “Left outer” join type the final output table will be as follows:
As a result, we get a merged table that consists of rows of table 1 and the additional Code column as required.
The highlighted rows containing “null” in the Code column were part of Table 1 but could not be found in Table 2.
Scenario 2 #
New table showing all countries, parts and quantity columns from table 1 against the matching rows in table 2.
Using the “Right Outer” join type, the final output table will appear as follows:
As a result, we shall get all the rows from table 2 even if the rows are not present in Table 1.
Against these rows from Table 2, Power Query will fetch Country, Part & Quantity column from table 1. Incase a match is not found in “Table 1” null shall be displayed.
Scenario 3 #
Table containing rows from both table 1 and table 2
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)
Scenario 4 #
Rows of Table 1 containing only matching rows with Table 2
Using the “Inner” join type the final output table will be as follows:
Scenario 5 #
Rows from Table 1 containing only rows not matching with table 2
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.
Scenario 6 #
Rows from table 2 not matching with table 1
Using the “Right Anti” join type the final output table will be as follows:
As a result, we shall get a output table containing only those rows of Table 2 which could not find a match in Table 1.
