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
atavo
Helper I
Helper I

SEARCH TEXT STRING BASED ON LIST OF VALUES - EXACT MATCH

Hi

 

There have been solutions offered regarding searching string text using a list of values, eg:

 

1. https://community.powerbi.com/t5/Desktop/Power-query-Add-column-with-list-of-keywords-found-in-text/...

2. https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/m-p/182148#M79842

 

...but these do not address the following challenge

PROBLEM

Text: "I LOVE MOTORBIKES".

 

List of Values: BIKES, BIKE, MOTOR, MOTORBIKE, MOTORBIKES

 

CHALLENGE: When I perform the comparison, ONLY "MOTORBIKES" should return TRUE.

 

Is there a way of doing this in Power Query

 

Thanks in advance

Alfred

1 ACCEPTED SOLUTION
atavo
Helper I
Helper I

Thanks
Was able to solve the problem by wrapping the string text in single spaces (b4 and after). Did the same with the list of lookup values. Thereafter, comparison worked like a gem.
Alfred

View solution in original post

4 REPLIES 4
giftchima
Frequent Visitor

Can you please show me how to do this? I am stuck. Thank you.

@atavo 

atavo
Helper I
Helper I

Thanks
Was able to solve the problem by wrapping the string text in single spaces (b4 and after). Did the same with the list of lookup values. Thereafter, comparison worked like a gem.
Alfred
Stachu
Community Champion
Community Champion

you convert the text to list and then use list comparators, e.g. List.Contains
https://docs.microsoft.com/en-us/powerquery-m/list-contains



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.

Top Solution Authors
Top Kudoed Authors