"Text Contains" Filter with List as Argument

[Anonymous]

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

[Ahmedx]

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]

pls try this

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

[JoshLevent]

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

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

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.

[maxit00]

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?

[JoshLevent]

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.

[maxit00]

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]

@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.⚡

[Anonymous]

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?

[edhans]

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.

[parry2k]

@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.