Check if Column Contains Item from List in Power Query - Create Text.ContainsAny!

This video shows how you can create a condition to check for multiple substrings in a string. In other words to test if a value contains any of multiple items.

It is very similar to the Text.Contains function, with the main difference that the shown methods are able to deal with multiple substrings. It's like a Text.ContainAny function to check if text contains one of many strings.

Master Functions and Syntax in M

Chapters:
0:00 Intro
0:22 Recap: exact match
0:56 Method 1: Splitter.SplitTextByAnyDelimiter
4:01 The issue with Text.Contains
4:50 Method 2: Leverage Text.Contains in a function

ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.

SUPPORT MY CHANNEL

SUBSCRIBE TO MY CHANNEL

LET'S CONNECT:

Thank you for your support!

#TextContains #PowerQuery #BIGorilla

[alexkim]

List.AnyTrue nesting with List.Transform is something mindblowing I learnt from this video. It saved so much time than performing join specifically just to reach the same output, despite it being the most "Excel" way of doing things.

[Lyriks_]

You reason like a developper yet you analyze like a business guy, which is an amazing combination ! GJ mate

[gabrielamorales]

Thank you! This is exactly what I need to do, you explained it in a magnific way, awesome!

[Special_Excel_F_and_L]

This is awesome. It just solved me a problem I wasn't able to do. I spent too much time on something I knew wasn't that complicated but couldn't figure it out. And that is was part of a major tool to reduce report output for our team, saving is major headache ! Thank you again !

[NikosGeorgosopoulos]

This was inspirational actually. the second method helped me with something that's been bugging me all day. Thanks

[vladsamoilov]

Amazing! Thank you so much Rick for sharing your knowledge. List functions are really useful. Keep doing great job!
Vlad.

[ozGold]

Excellent video. Very well explained. Thank-you so much.

[coventry-enterprises]

High-five to Rick!! Thank you for sharing these clever techniques.

[ExcelOffTheGrid]

Great video Rick. Two good methods for solving this issue. 👍

[dganalysis]

Hi Rick, I used this technique a few times since watching your video and it works great.

I have one query though - If my list contains multiple values separated by a delimiter, such as:
CompanyX, Company Y
CompanyX Asia, Company Y
CompanyX Europe, Company Y

and I want to search for occurrences of individual companies, is there a way to capture each company, without duplicating CompanyX when it assesses the the Asia and Europe subsidiaries?

Apologies if this doesn't make sense.

[yvesbrusten]

Excellent and very didactive video. Thanks a lot ! Season's greetings.

[Alan.DL]

Great stuff!

One quick question, would it be possible to implement this method but checking if the condition is met in more than 1 column at the same time? Because in both solutions you can only pick one column, if you try to create a list of the columns you want to check it doesn't work.

[grahamsnelson]

Thank you for the video, that really helped!

[redbcn]

Hi Rick! I am still new to PQ and your video helped me a lot. Thank you so much!

[rauljimenez]

Great video, great methods and good case uses for list functions, thank you!!

[GeertDelmulle]

Nog maar een paar van jouw video’s gezien, maar dit is echt goed! :-)
Subbed!

[faroma]

Man, you saved my life!

I really appreciate!

[JenMayB]

Thank you! What function would I use to return the value found and matched to my list? My list will be another table and quite large so i am trying to extract the found value from a large irregular text string.

[leebecker]

Thank you Rick! This is so useful. I love your teaching style!

[hseyedagha]

That was so useful, thank you for sharing 😊