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

How do i combine the lists count with list contains. I want to create a tag based on a bunch of key words. But max count of the category will be the main tag.

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 !

Thanks Rick, let me ask you a question. How can we tweak your solutions for a partial lookup in the list itself. Let's say on your master table with all the names you have two similar names like Carl and Carlos, and in your list you want to look for for any name that contain Carl. Can you tweak it ?

👏

How about if you have a list as a query? That's what I came here to find out. How would I prefer a list called say 'ABC' in Power Query's list of Queries?

💯👍

Thank You so much,
I find first method pretty neat.
Which one of two method is optimized in query stand of point?

Hi Rick Great solution for multiple partial match using List.transform but why am I getting an error with all the list using Excel for Mac?

Thanks man ! This one helped

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

great Content => first easy solution for filtering different multicharacter Criterias on a single text.

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.

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.

Great job! thanks for the video

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

Thanks alot

Hi Rick. How can I use this same function but reference an actual list of values that I have listed and exclude the rows that start with those values from my dataset?

Is it possible that instead of writing the list {} ,getting if from a separate query table as list?

I have a question that I have been searching the web for for awhile. I’m hoping you can help.
I was hoping to remove a list of about 170 customers from my query. I have their emails and that’s how I need to eliminate them. I know I can filter and copy and paste each and every email in to text does not contain but is there a way to copy and paste the whole list into the advanced editor ,not having to manually type all the and not text contains, etc around each and every email address?
Thanks in advance!

What code would you add to return the substrings that were found? For example, if I have a row with the strings "Carlos Weaver Bigge" and I am checking if the cell contains "Carlos" and "Bigge", how do I return in a new column a cell that says "Carlos, Bigge" ?

Your content is so valuable, thank you!
I managed to do something that was haunting me lol.

P.S. I followed you on Twitter but found no way to send you a dm, i have a specific question about something that I would like to add to my formulas

From the input values in our list, how do we know/get those found in the text?

This is excellent. I have this requirement. I have a list of server names named "Host Names" I need to fetch the server names from another column named "Description" using "Host Names" table and display it. It might have multiple server names as well under "Description". Could you please tell me how do that?

How do I execute this functionality but with everything case insensitive? Example: the full name column contains carl Dunn instead of Carl Dunn

Exactly what I needed thanks!

Perfect. Thank you very much. This is something that I have been looking for so long.
Just 1 question, instead of returning conditional check values like "A", "BBBBBB" what if we need the matched substring populated. what changes are needed in the function code then.
Thanks again. Liked and Subscribed <3 :)

Thank you. But how do you make it return the text part it has found instead of TRUE?

How could use you this to search an entire field? I have many values that have the same prefix but can have various lengths etc.

great. worked for a presentation at work thanks

This is excellent. I have a question though .... What if I wanted to get back the item that matched? e.g what if I want to return " Amir" when Amir was the one that matched?

Hi :)
Thank you, it helped alot :)

However this 3rd or even 4th different case for me where I could use a function out of this.
I know how to create functions,
However when creating the 1st step of a function:
(MyInput as text) =>
It seems there is no "Column" type input. Only Table, List, function, text

Is there a way to somehow Make it?

Killer work, sir

Im used to excel showing what info he needs in order for a formula to work. In power query you basically need to know what your doing.
All example videos are fine, but its just luck to find exactly what you need in order to use it, otherwise (in my case) i have to learn a lot more because this is SF to me.

Can we give another table reference... i have 1000 values to check whether text contains any of them.

how to retrieve a number start with some characters and in a free text format sentences. For eg. number starts with ABxxxxxxx.

Hi, thanks ! I have a column of 'List' how do I convert it to text without using the extract function because it slows down the data load so much. If there is another way I can transform that list to text of the actual contents would be greatful. Many thanks

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

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.

Can you use the same this method to check multiple lists? For example, if List.Contains {"a","b","c"} then "Group 1", if List.Contains{"d","e","f"} then "Group 2", If List.Contains {"g","h","i"} then "Group 3", else "Group 4" , [employee],

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

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

Nice, cleanest explanation

Instead of having the specific text like "Carl" and etc, can i use a column in the query ? If yes how ?

Qué buen video!

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.

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

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

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