VLOOKUP in Power Query Using List Functions

If you've done lookups in Power Query to pull values from one table into another, you may have used a query merge to do this.

Mynda has written previously on how to do an Exact Match in Power Query and an Approximate Match in Power Query

Here I'll be showing you how to use List Functions in Power Query to do both Exact and Approximate matches.

This approach requires a little more M coding but requires less steps and is my preferred method for doing lookups. But hey, I would think that, I'm a coder 🙂

Using List Functions

The key to doing this is remembering that table columns are lists, and as such you can use List Functions to manipulate the data in them.

Watch the Video

 

Download Sample Excel Workbook

Exact Match

My sample data is a table of sales on different dates for different types of food and drink. This table is named data.

What I want to do is add a column showing the category for each item. These categories are stored in a separate lookup table named categories.

I've loaded both tables into PQ and set categories to load as connection only because I don't need to create a new table from it.

So with both tables loaded into PQ, let's look at what we need to do.

The Food & Drink query needs to get the Category from the Categories table. By looking up the value in the Food & Drink[Product] column in the Categories[Product] column we can get the row number in Categories[Product] that matches.

Using that row number as an index on the Categories[Category] column will return the Category.

Let's do this step by step. Start by adding a Custom Column and calling it Category Index.

Using the List.PositionOf function, I want to look up in the Categories[Product] column, the value in this [Product] column.

The new column shows the position of each Product in the Categories table.

Remember that Lists are indexed from 0, so Apple Juice is in Position 0 of the [Product] column in the Categories table.

Atlantic Salmon is position 65 etc.

Now that I have this index number I can use it to lookup the category. Create another Custom Column and call it Category.

What I need to do here is enter a reference to the value in the Categories[Category] column. This is done by specifying the Category Index value inside curly braces after the Table[Column] reference.

Click OK and this creates the Category column in the Food & Drink table.

Let's do a litle tidying up, We don't need the category Index column now so delete it, and reorder the columns.

Before I finish though, I can make things a little neater. I like reducing my steps and code where possible.

You can see in the Added Custom step that the code creates the Category Index column

And the subsequent Added Custom1 step it uses the values from that Category Index column.

You can take the code List.PositionOf( Categories[Product] , [Product] ) from the Added Custom step, and replace [Category Index] in the Added Custom1 step with it.

This condenses the code from two steps into one and you end up with the same result.

As the Added Custom step is no longer needed, delete it. Also delete the Removed Columns step as all that is doing is deleting the Category Index column. But as the query is no longer creating that column, this step is not needed either. I don't actually need to create the Category Index column at all.

OK so the query is done, load to the sheet and that's our finished table.

Approximate Match

To do an approximate match I'm going to use this table of sales figures for various sales people and add a new column showing the bonus rate they'll get for those sales.

The idea being that you multiply the sales amount by the bonus rate to work out the bonus the sales person gets paid.

The bonus rates are stored in a separate table called excitingly, BonusRates.

Make sure the table is sorted in ascending order on the Threshold value. It's important to note that the first row has $0 threshold and a rate of 0.

The reason for this will become clear as I explain how the lookup query works

The bonus rate is determined by the sales amount. If you sell $10,000 or more, but less than $20,000 then the rate is 0.1

If you sell $20,000 or more but less than $30,000 then the rate is 0.15, etc

Load both tables in Power Query and set the Bonus Rates lookup table to connection only.

What I need to do here is of course look up the sales amounts in the BonusRates table. But this time I'll use List.Select to create a list of all values in the BonusRates[Threshold] column less than or equal to the Sales Amount

The number of elements in this list will be used as the index to lookup up the bonus rate.

I'll use the first sales value of $17,606 as an example. There are 2 values in the Threshold column less than or equal to $17,606.

The List.Select function creates a list containing 0 and 10000. Then by counting the items in this list, I can use that number to return the 2nd item in the Rate column which is 0.1, or 10%

Let's look at the code. Open the Power Query editor and add a Custom Column called BonusRates and add this code

I'll explain what's going on here

The variable val contains the Sales value in the current row of this table. Remember that the code written here is run for every row in the Sales column.

List.Select creates a list containing all values in the BonusRates[Threshold] column, that are greater than or equal to the value in val.

The each _ is shorthand for the current item in the BonusRates[Threshold] column. It's saying compare val with each item in the BonusRates[Threshold] column

The result of this code is a column of lists.

By clicking into a cell beside one of the lists, I can check what is in that list.

For the Sales value 9238 the BonusRates list contains just a 0, because the minimum sale amount to get a bonus is $10,000.

If there wasn't a row in the lookup table with 0 threshold then the list for any sales value less than 10000 would be empty. When the query tried to lookup an index using an empty list it would generate an error. Having the 0 Threshold row means that the code will always create a non-empty list and avoid such errors.

Checking the list in row 4 for the Sales value 32455 shows that the list contains 4 items, because this sale amount crosses the $30,000 threshold.

With this new column of lists I can now count the items in each list and lookup the bonus rate for the Sales amounts.

Add another custom column, call it Bonus Rate, with this code

What we need to do is lookup the value from the Rate column in the BonusRates table, and the number of items in the list created in the previous step is the index to that value.

Remember that lists are indexed from 0 so if list has 2 items then the 2nd item is at position 1. Therefore we have to subtract 1 from the count of items in the list

That's all I need so clicking OK and the new Bonus Rate column is created. I don't need the column containing the lists of bonus rates so I delete that leaving me with this final table.

I can now calculate the bonus amount in Power Query, or load this table to Excel and do it there.