Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ejlewis
Frequent Visitor

Check whether a string contains a value from another table

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:

freetextExpected result
Colletotrichum sp. Disease symptomsNo
Corynoptera sp small flyYes
Indet. IDNo
Megaselia sp not knownNo
Metopina sp Diptera flyYes
Neuroptera A. Lacewing eggsYes
 


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 🙂

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
danextian
Super User
Super User

HI @ejlewis ,

 

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

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.