#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

30 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

  4. What do I do if I need it to return values that requires it consider 2 different lookup Columns? Example Lookup Column 1 would be if the years match and then lookup column 2 would be if the store matches then it should return a specific value.

  5. I considered your approach and decided to do something else, which I can share.

    Merge, then Expand. I used the Table.SelectColumns function to grab only the lookup column and return column(s) from a table with many columns. I thought this might avoid slowness. I’m finding a match of ID in the table called LookupTable, and returning the value ReturnValue from that record.

    #”Merged Queries” = Table.NestedJoin(#”Sorted Rows”, {“ID”}, Table.SelectColumns(LookupTable,{“ID”,”ReturnValue”}), {“ID”}, “NewColumnName”, JoinKind.LeftOuter),
    #”Expanded NewColumnName” = Table.ExpandTableColumn(#”Merged Queries”, “NewColumnName”, {“ReturnValue”}, {“ReturnValue”})

  6. Thanks for sharing that.
    Is there any way to use this function to search the values on the same table?
    For an example, I need to take the value of a colum 24 rows before the current row and put on a new column, but this way the function is done I’m not allowed to search on the same table I’m including the new column.

      1. This is a sample (sorry, don’t know if there’s a better way to share it here):

        Index Unity Value
        1 Price 10
        1 Un 1
        1 Kg 2
        2 Price 5
        2 Un 2
        2 Kg 1
        3 Price 10
        3 Un 3
        3 Kg 6
        4 Price 8
        4 Un 1
        4 Kg 1
        5 Price 10
        5 Un 1
        5 Kg 1

        And the result I need:
        Index Price Un Kg
        1 10 1 2
        2 5 2 1
        3 10 3 6
        4 8 1 1
        5 10 1 1

        Thanks in advance for your help.

      2. Hi again – you can do this by using the Table.Pivot function –

        = Table.Pivot(x, List.Distinct(x[Unit]), “Unit”, “Value” )

        where x is the table

        /Erik

  7. Hi Erik,
    Very well explained, I’m new to this and I could understand exactly what was happening.
    You are the only person that talks about these, I have a question though, I hope you can help me with this.
    I tried your function (last bit at the end where you include it inside another table), which was very helpful by the way, but this function will only find match it if is exactly the same as the other table.

    I was wondering if you knew a way to search inside the text, if found, search inside the other table and return the column, if that makes sense.

    So using your example above:
    If found 12 in a cell, instead of saying ” not found”, return “100 75”.

    I found a partial solution to this but, it will only return the same word if found in a list (not case sensitive), I’m not sure if it will help but it might help if someone like me is trying to find a solution to this problem:

    List.Accumulate
    (
    List,

    “”,

    (state, current) =>

    if Text.Contains([Details], current, Comparer.OrdinalIgnoreCase)

    then state & ” ” & current

    else state

    )
    Thank you 🙂

    1. Hi Pierre – thank you for your feedback –

      When you refer to “12” to get “100 75” – – I guess you refer to the prices for productkey 1 and 2 in my example ?

      You will have to have the product keys separated by a ; or # to be able to find and return the prices – if the product key is 11 it will be impossible to split correctly.

      If that is possible I am sure I can create a working method – let me know if you want an example on that

      1. Hi Erik,

        Thanks for coming back to me so quickly.

        You are right, it would be impossible if it was numbers, I just realised that.
        I was just using your example above.

        In my case it’s words, so for example, I have a table with 2 columns, one column with keywords(ingredients) such as:
        Apple, carrot, watermelon, beef …
        And another column next to it, called Category such as:
        Fruit, Vegetable, Meat …

        What I would like to do is to search for these keywords on each row of the column and if found return the category, so for example:

        Cell contains: “Apples Beef”
        The next column which has the formula would return ” Fruit Meat”
        This would ignore any ending such as the “s” at the end and would return multiple values in one cell.

        The formula above only helps me find the words inside each cell, but I would like to go an extra step and combine your function to return the other column just like VLookup.

        The problem is that I’m new to Power Query and I’m working on this project to learn more about Power Query in the process.

        I hope this makes sense 🙂
        Also sorry for double posting for some reason I couldn’t see my first comment so I thought it didn’t go through.

  8. Hi Erik Svensen,

    Would it be possible to combine this solution and a formula that will search from a list of words inside every cell?
    An example using your data above would something like this:
    A cell contains “245” and the column would return “75 80 400”

    I found a solution to search for the string inside the cell but I don’t know how to combine it with this solution.
    Could you help me, please?

  9. Hi Erik,

    I have been scouring the internet, whilst not knowing what I am looking for exactly and I think this post is as close to what I’m looking for as I am going to find. In my case, I am looking to use this feature but with the data meeting two lookup conditions (someone earlier in the chat had this same query).

    In my case, I have a list of items (derogations) that have a document reference and a received date:
    derog 1 – docref1 – received date
    derog 2 – docref1 – received date
    derog 3 – docref2 – received date

    I also have another query that contains the document reference, the revision number and the published date:
    docref1 – rev0 – published date
    docref1 – rev1 – published date
    docref1 – rev2 – published date
    docref2 – rev0 – puclished date

    I would like to create a calculated column in my first table with the revision number, based on the docref and the recevied date:
    derog 1 – docref1 – received date – rev0
    derog 2 – docref1 – recevied date – rev2
    derog 3 – docref2 – recevied date – rev0

    Is this possible and what is the best way to define my lookup table? i.e. having one published date or having a from/to range? If needed, I can provide a sample dataset.

    Thank you very much in advance,
    Scott

  10. Hello Erik,
    Could you, please, advise if it is possible to return value upon multiple different lookup columns?
    Picture this,
    Lookup table has columns:
    ReturnedValue – Level1 – Level2 – Level3
    value1-L1.1-null-null
    value2-L1.1-L2.1-null
    value3-L1.1-L2.2-null
    value4-L1.1-L2.1-L3.1
    value5-L1.1-L2.1-L3.2
    Fact table has columns:
    Level1 – Level2 – Level3 – Other columns
    L1.1-null-null-…
    L1.1-L2.1-null-…
    L1.1-L2.2-null-…
    L1.1-L2.1-L3.1-…
    L1.1-L2.1-L3.2-…
    I need to define single corresponding ReturnedValue for each row of the Fact table.
    Expected outcome for the Fact table:
    ReturnedValue – Level1 – Level2 – Level3 – Other columns
    value1-L1.1-null-null-…
    value2-L1.1-L2.1-null-…
    value3-L1.1-L2.2-null-…
    value4-L1.1-L2.1-L3.1-…
    value5-L1.1-L2.1-L3.2-…
    Logic: lookup by level1. If multiple values found (count of ReturnedValue >1), then lookup by level1 and level2. If still multiple values found (count of ReturnedValue >1), then lookup by level1 and level2 and level3. (I actually might have more levels, this is just for the illustration).
    Thank you very much in advance!

      1. Erik,
        Thank you for your feedback!
        The problem is that I have a very large amount of rows that will grow over time and those level lookup columns are text columns with sometimes many symbols (1-5 words). So I’m afraid for the performance in this case and thought it would be better to do this in Power Query. What would be the best approach?
        Thank you.

      2. Hi Stew – the number of rows does not have to be a problem but that depends on the source and if it is text/csv files it is a problem but if it is SQL server or another source that supports query folding then you can push the hard work to the source instead of power query – so as always it depends 🙂

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