Lookup multiple columns in Power Query

[ERD]

Hello Power BI community!

I'd like to share with you one of the ways to lookup multiple columns in Power Query (you might use VLOOKUP function if you're an Excel user).

There are some cases when merging queries is not an option / causes performance issues. But there is an alternative: by using the M language we can make a lookup function.

Here is an example of data:
Data table:

Lookup table:

Steps:

1) Add a new blank query
2) Functions to be used:

• Table.RenameColumns | This step is needed to avoid any problems with looup table column names
• Table.Column | Returns the column of data specified by column from the lookup table as a list
• Table.PositionOf | Returns the row position of the first occurrence of the row in the lookup table
• Table.SelectColumns | Returns the table with only the specified columns.

3) Add the next code to the blank query:

(
// lookupValue - the value to find
    lookupL1Value as any, 
    lookupL2Value as any,
    lookupL3Value as any,

//lookupTable - the Table/Query to lookup in
    lookupTable as table, 

// lookupColumnName - name of the column to lookup the value in
    lookupL1ColumnName as text,
    lookupL2ColumnName as text,
    lookupL3ColumnName as text,

// returnColumnValue - name of the column from the table to return
    returnColumnValue as text
) =>
let

RenamedColumns = 
    Table.RenameColumns(
        lookupTable, 
        {
            {lookupL1ColumnName, "L1Cln"},
            {lookupL2ColumnName, "L2Cln"},
            {lookupL3ColumnName, "L3Cln"}
        }
    ),

colToReturn = Table.Column(RenamedColumns, returnColumnValue),

lookup = 
    Table.PositionOf(
        Table.SelectColumns(RenamedColumns,{"L1Cln", "L2Cln", "L3Cln"}), 
        [   L1Cln = lookupL1Value,
            L2Cln = lookupL2Value,
            L3Cln = lookupL3Value
        ]
    ),

 Result = if lookup >= 0 then colToReturn{lookup} else "Not found"
in
 Result

4) Go to your Data table, click Add Column - Invoke Custom Function

5) Fill in the fields and click Ok

Result:

If you believe that the function can be impoved in any way, please, share your thoughts.

Kind regards,

ERD

CustomFunctionMultipleLookup.pbix

[PowerBIhacker]

Hi, thanks for this it is super useful and easy to use! Could there be a way to speed up invoking the function? When I click on the step it takes a long time to load each time. Either something to add to the function, or a setting I do not know? I am matching a 1000 row table with another 1000 row table.