Check whether a string contains a value from another table

[Anonymous]

I have as a list (about 900) of values that I am interested in:
Genus Table:

Genus of interest

Metopina

Neuroptera

Corynoptera

…

In a separate table, I have free text (lab results). This is a very large dataset. 

I want to search the free text to see if they have any value in the genus table, and return a new column with Yes/No (or True/False, whatever.) 
Lab results table:

freetext	Expected result
Colletotrichum sp. Disease symptoms	No
Corynoptera sp small fly	Yes
Indet. ID	No
Megaselia sp not known	No
Metopina sp Diptera fly	Yes
Neuroptera A. Lacewing eggs	Yes
…

I tried modifying the solution from this post but it is returning an error for me:

= Table.AddColumn(#"Inserted Merged Column", "Genus", each List.First(List.Select(Genus Table, (x) => Text.Contains([freetext], x))))

I can write DAX pretty well but I don't know M at all so I've probably just mucked it up, but would appreciate some help 🙂

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Contains(Genus[Genus of interest],[freetext],(x as text, y as text)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase)))
in
    #"Added Custom"

Hope this helps.

[danextian]

HI @Anonymous ,

Here's what I would do:

• duplicate the freetext column
• lowercase the duplicate (or uppercase) as M is case-sensitive
• remove special characters from the duplicate
• and then do the search using List.Position

Please see attached pbix for details