How to perform VLOOKUP in Power Query without using Merge Join in Power BI | Power BI Blog

How to LOOKUP values in Power Query without using Merge Join in Power BI

Scenario:

Suppose we have two tables “ds_SampleStrings”, “ds_UniCharsLkp” as follows.

ds_UniCharsLkp : 

It will be the lookup table using in our scenario, which contains the information of Unicodes for the Alphabets and Special Characters.

It will be our sample table into which we are getting the information from above Lookup table.

This can be achieved with use of “List.PositionOf()” function inside a Custom Column as per below:

cc_StrCode = Table.AddColumn(sht_Data, “StrCode“, each 

                    try(ds_UniCharsLkp[AlphaCode]{List.PositionOf(ds_UniCharsLkp[AlphaChar], [Str])})                       otherwise “Not Available”)

Notes:

In the above logic, we are doing the lookup based on ds_SampleStrings[Str] column into lookup table column ds_UniCharsLkp[AlphaChar], and then returning the values of ds_UniCharsLkp[AlphaCode] based on the position of  ds_UniCharsLkp[AlphaChar] in lookup table.

The try() and otherwise () functions will helps in trying to perform lookup and return value otherwise it returns the “Not available” for not matching record.

#Result:

Now lets Lookup and get the values of SpecialName based on the “SpecialStr” in our sample table. If no match found in ds_UniCharsLkp , then it should return as “Not available”

cc_SpecName = Table.AddColumn(cc_StrCode, “SpecialName”, 

                     each try(ds_UniCharsLkp[SpecialName]{List.PositionOf(ds_UniCharsLkp[SpecialChar], [SpecialStr])}) otherwise “Not Available”)

#Result: