M-Code Text.Contains Between two tables

Hi @Melissa ,

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:

Refence{[Code=[Desc]]}[Category]

Could you please advise?

Please find the attached excel files.

Test File.xlsx (19.2 KB)
Decription.xlsx (8.3 KB)
Reference.xlsx (9.2 KB)

Thank you Matty

Hi @Matty,

Give this a go. You can avoid hard coding and multiple if-statements.
Note I’m also ignoring case.

here’s your sample file. eDNA - TextContains lookup.pbix (18.1 KB)

I hope this is helpful.

3 Likes

Thank you @Melissa !

WOW your formula ignores the extra step that I applied → transforming everything to lower case.

I am geussing “Comparer.OrdinalIgnoreCase” → ingore case sensitivity .

What does it do the question mark “?” at the end of the formula?

Hi @Matty,

Q1 =Yes, and Q2 for that you can review, this video.

1 Like

Thank you @Melissa :slight_smile: M=Melissa Code :smiley:

1 Like

@Melissa

Hi Meliisa,

I did replicate your code, but run into small issue:

the question mark didn’t worked in PQ (Excel) correctly it returns error instead of null.

image

What is the reason behind it?

My guess would be you don’t have a BufferReference table?
Hard to trouble-shoot with just this picture

@Melissa

I do have BufferTable,

The error that I get using:
Table.SelectRows(BufferReference, (IT) => Text.Contains([Description],IT[Description],Comparer.OrdinalIgnoreCase)){0}[Allocation]?, type text )

image


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 ))

image

Did you also include the try clause?

each try Table.SelectRows(BufferReference, (IT) => Text.Contains([Description],IT[Description],Comparer.OrdinalIgnoreCase)){0}[Allocation]?, otherwise null

1 Like

Thank you @Melissa it worked now!!

each try Table.SelectRows(BufferReference, (IT) => Text.Contains([Description],IT[Description],Comparer.OrdinalIgnoreCase)){0}[Allocation]? otherwise null, type text )

image