Since VLOOKUP is one of the most popular Excel functions it makes sense that one of the first things you want to do in Power Query is VLOOKUP. But step away from the Add Custom Column button because there’s not a formula in sight.
Excel Power Query VLOOKUP is actually done by merging tables. Makes sense if you think about it, after all a VLOOKUP is simply pulling a column from one table into another table.
This tutorial applies to Excel 2010 onwards and requires the Power Query add-in, or if you have Excel 2016 you'll find it on the Data tab in the Get & Transform group.
Our data is in a simple table called “data” (nothing like stating the obvious), containing a date, product name and sales amount:
We have a lot of different products and I’d like to group them into categories so they’re easier to analyse.
In comes table number 2, called “categories”, which maps my products into their respective categories:
An aside, if you were to use VLOOKUP formulas to bring the Category name into the "data" table you’d have to either switch the column order in the "categories" table so Product was in column A and the Category in column B, or use INDEX & MATCH, or some other clever manipulation of VLOOKUP to make it lookup to the left.
Not with Power Query, it's not fussy about column order, as you'll see.
Excel Power Query VLOOKUP
- Format your two tables as an Excel Table (CTRL+T and make sure they have headers)
- Load the data table into Power Query: Excel 2010/2013 Power Query tab > From Table, or Excel 2016 Data tab: Get & Transform group > From Table
- Close the Query: Home tab > Close and load to > Connection only
- Repeat steps 1 through 3 for the categories table
- In the Workbook Queries pane in Excel right-click the data query > Merge:
- In the Merge dialog box select the categories table in the bottom section:
- Left click your mouse on the Product column in the data table. It should turn green.
- Left click your mouse on the Product column in the categories table. It should also turn green. This tells Power Query which columns to match up.
- The Join Kind will default to ‘Left Outer’ which is fine since I want to make sure all of the records in my first table (data) are retained even if there isn’t a corresponding category for a product.
Click OK, which will open the Power Query editor window:
You can see we have the first table (data) in the first 3 columns and then a NewColumn which is effectively our second (categories) table.
- Click on the double headed arrow on the NewColumn. This displays a list of columns in the categories table. Uncheck the Product column since we already have this in our data table. Also uncheck the ‘Use original column name as prefix’ and click OK.
- Now we have a new table which includes the product category column:
- I’ll do some tidying up:
- Drag the Category column in between the Date and Product columns
- Format the Date column as Data Type: Date:
- Format the Sales column as Data Type: Currency
- Close and Load To a Table:
And now you have a new table in an Excel worksheet containing your 4 columns ready for analysing in a PivotTable or formulas.
I know that seems like a lot of steps but it actually only takes around 1 minute to set it up when you know what you're doing, as you can see in this video (no sound):
And the best thing is if your source data changes (new data, new categories, new anything), you can update the query by clicking the Refresh button on the data tab.
Download the Workbook
Power Query VLOOKUP Approximate Match
Replicating a VLOOKUP Exact Match formula in Power Query is easy, as you've seen above, however replicating a VLOOKUP Approximate Match formula in Power Query isn't, but that's a post for another day.
More Power Query
The Definitive Guide to Power Query; what it's good for, what versions of Excel can get it, where to download it and more.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.