If I do survey to identify what are the most frequently used formulas in Excel, I am pretty sure that I would get “VLOOKUP” in top three. VLOOKUP is so much widely used in excel that there won’t a single day left without using it. What “GOOGLE” is to internet “VLOOKUP” is to Excel. It lookups up the data from different sources and merge it in a single table. Yes, I agree that VLOOKUP saves so much of time while consolidating data but it has some limitations. Viz.
- It can lookup only values from one column and not multiple columns at one go.
- It can pull in the values from other table / source only for one column.
- It can get the data only from the columns on the right side.
- It will pull in the data which appears first and not all entries.
- It will consume memory and slows down the file calculation in large data set.
- We will have to manually do cross lookup on both data set if need to find differences.
…and many more.
Despite these limitations it is still widely used. To overcome few limitations there are workaround using INDEX & MATCH formulas together or using some helper columns to get the desired result. Till now there was no easy alternative which could overcome these limitations.
With introduction of different JOINS in Power Query these limitations can be resolved. Here is brief summary of what these JOINS are:
In Database or SQL world, data from multiple sources is joined together using these JOIN concepts. In this post and video below I would briefly explain you how you can perform these JOIN / MERGE queries using Power Query and how it will address most of the limitation of VLOOKUP, INDEX & MATCH formulas.
Note: These JOINs will perform EXACT match on columns and not approximate match.
Summary of what these different JOINs in Power Query are and what they performs:
- It will merge two different queries (which are linked to Excel Tables / sheets / text files, etc.)
- While merging data it can consider one or more columns for lookup between queries.
- It will pull in all the rows / records from another query and not only first record.
- It will look data from any side.
- It will pull in all the columns from other table by default and we can manually select which column is needed.
- It is EXTREMELY fast as compared to VLOOKUP, INDEX & MATCH and does not consume significant memory.
Now, let’s us understand some theory about these JOINs before actually performing these in Power Query. People from Database or SQL background will easily understand from above picture what these JOINs are all about and what will they do when we execute it. However, for regular excel user at the outset it will be little complicated but when you relate these concepts with your usage in VLOOKUP it would look simple and feel why this was not introduced by Microsoft earlier.
Let’s start visualize you have these two tables with certain rows and columns.
You want perform following tasks with these two tables:
- You want to merge information in Table 1 & Table 2 for matching records. – Use Full Inner JOIN
- You want to merge details from both the tables. – Use Full Outer JOIN
- You want to pull in information in Table 1 for matching records from Table 2. – Left Outer JOIN
- You want to find records in Table 1 which DO NOT match Table 2. – Left Anti JOIN
- You want to pull in information in Table 2 for matching records from Table 1. – Right Outer JOIN
- You want to find records in Table 2 which DO NOT match Table 1. – Right Anti JOIN
So here are the brief steps how to perform these JOINs / MERGE queries in Power Query:
- Bring in the data from Table / sheets into Power Query. Let’s assume you give that query a name – Table 1 and Table 2.
- Go to Merge Queries
- Select Table 1 and Table 2 from drop downs on screen.
- Select the columns from Table 1 and Table 2 which should be used for merge the details. Note: You should select columns to be used in SAME sequence in both the tables. For easy reference “1,2…” will be suffixed to column names in sequence in which you selected them.
- Select the type of JOIN you want to perform on above tables and click ok.
- Merge data will be loaded into Power Query window. You need to give a name to query and the click close & load.
Here you have your merged data in the way you wanted.
Now you can have a look at below video to see this in action:
<video will be added soon>
Guys let me know your thoughts in comments section what you liked and not liked about the video.