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