Using the mcode I would like to return the category from “refence” table instead of using if statement.
At the moment I used the below code:
if Text.Contains([Desc], “bank fee”) then “Finance” else if Text.Contains([Desc], “fire call”) then “Fire” else if Text.Contains([Desc], “deposit”) then “Finance” else 0)
I would like to replace the above with Text.Contains using other table “Refence”
If we would look for an exact match we could use the below:
The error that I get using:
Table.SelectRows(BufferReference, (IT) => Text.Contains([Description],IT[Description],Comparer.OrdinalIgnoreCase)){0}[Allocation]?, type text )
The code is returning the result, but if the allocation doesn’t exist in the lookup table then it returns an error.
I tried other two solutions that I thought might work:
Using “otherwise”
Table.SelectRows(BufferReference, (IT) => Text.Contains([Description],IT[Description],Comparer.OrdinalIgnoreCase)){0}[Allocation]? ,otherwise null, type text )
Then I thought I could wrap the function using “Table.ReplaceErrorValues” but again it didn’t work:
Table.ReplaceErrorValues(Table.SelectRows(BufferReference, (IT) => Text.Contains([Description],IT[Description],Comparer.OrdinalIgnoreCase)){0}[Allocation]? , type text ))
each try Table.SelectRows(BufferReference, (IT) => Text.Contains([Description],IT[Description],Comparer.OrdinalIgnoreCase)){0}[Allocation]? otherwise null, type text )
M=Melissa Code 
