#PowerQuery – Replicate doing an Excel VLOOKUP in M

Power Query has a lot of built in functions, but it doesn’t have a function that exactly matches the VLOOKUP in Excel – but by using the M language and other M functions we can certainly replicate some of the VLOOKUP functionality and make an even more flexible lookup function.

Now the example data is like this

In Excel we would lookup the price for at specific productkey by using this formula

– in this case ProductKey 1 with a price of 100.

In order to replicate this in Power Query we can use the function List.PositionOf

So I add a new blank query

And then use the function List.PositionOf – with the following arguments

List – Is the column ProductKey from my lookuptable Products – refer to like this Products[ProductKey]

Value – Is the value to look in this case the value 1

Occurrence – Is set to 0 to only return one value

This will return the position of the value in the list – sort of like using the MATCH function in Excel

Now to return the price – we can use this result to lookup the price like this

= Products[Price]{List.PositionOf(Products[ProductKey], 1, 0)}

And we get 100 returned which is the price of productkey 1.

The formula is structured like this

=NameOfTheTable[NameOfTheColumnToReturnTheValueOf]{PositionReturnedByListPositionOf}

But we why not change it into a function in PowerQuery so we use the function on all rows in a table or on any table.

The function can be created like this

The code

(lookupValue as any, lookupTable as table, lookupColumnName as text, returnColumnValue as text) =>
let
// lookupTable= Products,
// lookupColumnName = "ProductKey",
// returnColumnValue = "Price",
// lookupValue = 1,
 colLookup = Table.Column(lookupTable, lookupColumnName),
 colToReturn = Table.Column(lookupTable, returnColumnValue),
 lookup = List.PositionOf(colLookup, lookupValue, 0),
 Result = if lookup >=0 then colToReturn{lookup} else "Not found"
in
 Result

The function takes 4 arguments –

lookupValue – The value to find – can be any type

lookupTable – The Table/Query to lookup in

lookupColumnName – The name of the column to lookup the value in

returnColumnValue – The name of the column from the table to return

The colLookup is a variable that uses the function Table.Column to return a list of values in the lookup column.

The colToReturn is a variable that uses the function Table.Column to return a list of values from the values you want to return column.

The lookup variable uses the List.PositionOf to find the position/index of the search value in the lookup column.

Result will use an if statement to test whether a position is found and if so returns the value at the position in the colToReturn list – other wise returns the text “Not Found”.

After this we can use the function in other tables to lookup the for instance the Product price like this – by added a invoke Custom Function Column

OBS – I haven’t tried this on a large table so be aware of any performance issues.

Hope you find this useful – and happy Querying

Here is a link to an example file – Link

5 thoughts on “#PowerQuery – Replicate doing an Excel VLOOKUP in M

  1. Pingback: Replicating VLOOKUP with M – Curated SQL

  2. Superb! I’m wanting to identify “sequences” in a table where a sequence is a run of records with the same value in a specific field, for example if the following were the list values in Field A {0,4,8,2,2,2,6,4,7,7,7,3,2,2,2,2,4,5} then I want to find the three sequences: 2,2,2 and 7,7,7 and 2,2,2,2. The difficulty I have is that independent sequences may have the same value (the 2s in the example). Using your function and an iterative “look back” I can now do my identification and assign each sequence the index value of the record at the start of the sequence. Thanks!

  3. Big thanks for very detailed explanation, especially for pbix file.
    I have applied this fx onto data base with 158k rows, which is coming initially from xls file of 25mb.
    However, refresh of data is lagged afterwards, it says that updating data from xls file of 600 mb and keep counting up, instead of previous 25mb…
    I fairly don’t understand logic of having fx function in query and increasing data within xls file, so will have to find another solution..
    But otherwise it is really helpful feature.

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s