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.

ds_SampleStrings

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

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

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:

Notes:

Please use this kind of Lookup only for simple lookups on a small tables. It may not be the good choice on Large tables.

——————————————————————————————————–

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

——————————————————————————————————–



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*