
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
Be the first to comment