This M Code function allows you to lookup values on other rows in the same table or another table. This benefit of this process is that it allows you to use custom or unique indexes.

Create A New Blank Query

Create a new blank query in Power Query

Advanced Editor

Open the Advanced Editor

Paste the following code into a blank query named "fxLookUp":

(lookupValue as any, lookupTable as table, lookupColumnName as text, returnColumnValue as text) =>
let
 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

Custom Column

Within your other Power Query process, create a new Custom Column and paste a column expression similar to below:

fxLookUp([Your Lookup Value],#"Changed Type","Your Index Column","Your Value Column")

The second index (#"Changed Type") being your previous Power Query Step Name


  • No labels