Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to apply VLOOKUP in Power Query editor?

I need to check if a value of one column exists in another column of the same table.

I need to apply VLOOKUP in Power Query. 

With DAX, its possible but with Power Query, I am not able to find a proper solution.

Can someone please help me out?

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    In Power Query functions can be created like this:

    (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

    Or

     

    You can duplicate your query and merge the two queries to get the desired columns.

    Disable the load from the duplicate one to avoid performance issues.

     

    Please refer to the following documents for more information.

    #PowerQuery – Replicate doing an Excel VLOOKUP in M – Erik Svensen

    How to do a real VLOOKUP (false) in Power Query or Power BI – The BIccountant

    VLOOKUP / XLOOKUP in Power Query - Bing video

    VLOOKUP in Power Query Using List Functions - Bing video

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

     

  • Jon_vB's avatar
    Jon_vB
    Advocate II
    1 year ago

    I was trying to do this all in M code - because I didn't want to make the actual table and then join ... wanted it all in the M query (Advanced editor).   **My lookup table has no duplicates (in fact, there's a step to remove duplicates just to be sure.  If there were duplicates, the code below would not be perfect.

     

    I landed with this code that is working:

    CombinedTable = Table.AddColumn(TableAlmostDone, "UserResponsible_StaffKey", each DimMarketStaff[StaffKey]{List.PositionOf(DimMarketStaff[StaffName], [UserResponsible])}),
    FinalTable = Table.ReplaceErrorValues(CombinedTable, {{"UserResponsible_StaffKey", 0}})
    •   TableAlmostDone is the FactTable that I'm building
    • DimMarketStaff is like a temp table in my M code that just has the StaffKey and StaffName
    • The Fact table has the staffName, but not the Key ... so - this code uses the StaffName from the FactTable to lookup the Key on the Dimension table.   (because the Power Apps developer has the name on the Fact Table)