How to do a Vlookup in PowerQuery

Vlookup is one of the most common tasks in excel and of-course you can always write a Vlookup formula for that but doing a Vlookup using Power Query has 2 main wins

1. Vlookup in Power Query is really fast
2. It’s automated, you just have to do this once

 

If you did not know, you can do a really fast vlookup using excel too but this post is about how can you do a Vlookup in Power Query

 

Watch the Full Video

 

Assume 2 Data Sets

Transactions Table (where you’ll typically write the Vlookup formula) & Source Data Table (where the search will be made)

IMPORTANT NOTE – For performing a Vlookup, both these data sets have to be loaded as queries in the same excel file.

 

Step 1 Loading Source Data as a Query

In the file where you have to perform Vlookup (i.e. transactions table file) perform the following tasks

1. Go to Data (in excel 2016, power query is in Data Tab, else you’ll have a separate power query tab)
2. Get Data
3. From File
4. From Workbook

 

Import the source file (in this case – Data) in the Import Box

 

Select the sheet (in this case – Sheet 1) in which contains the data and hit on Edit

 

1. You’ll see all the data gets loaded in your query editor, I have additionally given a better name to my query (i.e. Data)
2. Then in the Home Tab click on Close and Load To
3. And then choose Only Create Connection

 

You’ll see the query will be displayed in the Query’s Pane

 

Step 2 Creating a query for Second Data

Remember I mentioned that both data sets have to be loaded as queries in the same excel file

1. Click anywhere in your transactions table
2. Data Tab
3. From Table/Range

You’ll see the transactions table will be loaded in the query editor

 

Step 3 Performing Vlookup

Finally its time now for Vlookup. Here is how you do it, once you have loaded source data and transactions data in power query

1. Click on Merge Queries in the Home Tab
2. Select the second table as Data in the Merge Box
3. From Transactions Table select the common column (tr code)
4. From Data Table select the common column (tr code)
5. Make sure the Join Kind is Left Outer (which is equivalent to Vlookup)
6. Ok

 

You’ll see a separate Data Column along with transactions numbers

1. Click on the double arrow to expand the column names
2. Choose the columns that you need (in our case value and manager)
3. Done!

 

Vlookup Completed

1. You can see that you now have Value and Manager looked up from the Data table
2. Just click on Close on Load and Power Query will dump this data on a new sheet (in a few seconds)

 

Automation

2 Super cool things that will happen

1. If you add more data to your Data Source or your Transactions Table, you just have to right click and refresh the query and the results will be updated
2. You can also dump a new file (named Data) in the same folder and again on refreshing the query the result will be updated

 

 

Other Power Query Tutorials

1. Convert Multiple Columns in Rows
2. Vlookup Horizontally for Multiple Records
3. Rank Based on 2 Conditions
4. Repeat Row N Times