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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

"Text Contains" Filter with List as Argument

Hi,

I'm certainly not a power query expert.

I would like to filter a query for rows that contain any substring contained in a list. Ive used the following, with success, to filter for exact matches, but I need to find rows which only contain any substring in a list of substrings.

= Table.SelectRows(QueryToBeFiltered, each List.Contains(ListOfArguments, [ColumnToBeFiltered]))

 

 I don't need to apply this dynamically, but that would be a plus.

I appreciate any help on this.

 

Thanks,

Chris

10 REPLIES 10
Ahmedx
Super User
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RiWCKqxuH34JqaP-R?e=0Mzoqx

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Ahmedx
Super User
Super User

pls try this

Table.SelectRows(#"Changed Type",(r)=> List.Count(Splitter.SplitTextByAnyDelimiter(MyList)( Text.Combine(Record.ToList(r))))>1)
JoshLevent
Advocate I
Advocate I

It seems like the other responders didn't really understand your question.

 

As far as I can tell, what you are trying to do is search for more than 1 substrings in every cell of a column.

 

So for example, you have a table

1abcdefg1234567
2abc123
3adg147
4efg567
5aceg1357
6bcd234

 

You want to return rows that contain any of the following substrings: 123, 567, 135.

This would match rows 1, 2, 4 and 5, but not rows 3 or 6.

 

So, how can you do it? There is a List.ContainsAny function which almost does what we want. We would want a Text.ContainsAny, that allows us to enter a list as the second argument. Unfortunately, this function does not exist. So instead, we have to run the Text.Contains function for each item in our list. A good way to do that is using List.MatchesAny which returns true if some function on each item of the list returns true at least once.

 

Here is the code:

 

Table.SelectRows(TableName, (Row) => List.MatchesAny(SubstringList, (Substring) => Text.Contains(Row[ColumnToBeFiltered], Substring)))

 

 

So what happens is that for each row we do the following: for each Substring, we check if the ColumnToBeFiltered (of the current row) contains that substring using Text.Contains. Then we have a list of true and false values. which is run through List.MatchesAny, which returns true if there is at least one true in the list.

 

(Parameter) => Expression are inline functions, where the entire evaluation context is saved in the parameter. So "Row" contains a row since the Table.SelectRows function iterates over rows in a table. And "Substring" contains an item from "SubstringList" since it iterates over that list. You could name those parameters however you want. TableName, SubstringList and ColumnToBeFiltered on the other hand refer to things outside this expression and should be adapted accordingly.

 

Note that in the documentation both Table.SelectRows and List.MatchesAny use the "each" keyword. These cannot be nested in this situation. "each [column]" is actually a shortening of "(_) => _[column]", the "_" is implicit. In this case if I used each for the row and the substring, then when I would try to use "_" to refer to the substring, it would attempt to give me the row, and I get an error.

 

I hope that helps.

Hi Josh, this is exactly what I am trying to do with my data and what you've proposed seems completely logical. I tried to implement it adapting the parameters needed. However, I get the error that PQ can't convert a value of type Record to type Function. In the details of the error the value is my first row of the query to be filtered (that's the first row from TableName for you) so I guess it's trying to convert it to a function and it can't. Can you perhaps help me with that?

The output of both functions must be a logical value. If this is not the case you will get a "cannot convert type X to type logical". The error you are getting is a little more unusual. It means that the parameter is given directly as a record where it expects a function that returns true or false. I can get this error if I do something like 

Table.SelectRows(Table, Table{0})

The {} braces call a specific row (A row by itself is always given as a record in Power Query). But I guess you're doing something else. Always an infinite amount of ways for something not to work.

 

Here is a demonstration PBIX file to play with: https://1drv.ms/u/s!At7O-RqLrIRtga0hgopcBpVFyDBpfA?e=WWpz28

 

If it doesn't help, feel free to send your code.

Thank you a lot for the really prompt response! I managed to solve the problem by splitting the line into smaller steps to see where it all comes from. It turns out I had just snuck brackets in the last argument Row([ColumnToBeFiltered]),... which broke everything. Thank you anyways, I hope I could help others with my mistake as well. And thank you for the great explanation of the code!

parry2k
Super User
Super User

@Anonymous yes you can do it, you can create a list seperate or within the same query and use it

 

= Table.SelectRows(#"Changed Type", each (List.Contains({"Value1", "Value2"}, [Column1])))

or

= Table.SelectRows(#"Changed Type", each (List.Contains(MyList, [Column1])))

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

If I understand this correctly, I would make the column to be filtered into a list, so that the List.Contains function can be used with it?

-Make column of interest into list

-Use List.Contains to tell me which entries in this list contain any substring which is contained in my 'argument' list

 

How do I make a list within my existing query without losing all my other columns?

You create a reference to the original query, then make the single column you want as a list, then use that list in your List.Contains.

 

Your original query is unaffected.

 

See this article I wrote on this a few weeks ago that may help. It specifically mentions how to create the reference then "drill down" to create the list.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@Anonymous everything is pretty straight forward, rather than going in circles, if you can share sample pbix file, I will put together something quickly for you and then you can take it from there, make sense? You can share thru one drive/google drive.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.