Forum Discussion
Filtering text by n values
To search multiple values in a string, you need to custom a function like:
Text.ContainsAny = (string as text, list as list) as logical => List.AnyTrue(List.Transform(list, (substring) => Text.Contains(string, substring)))See a similar thread below:
https://stackoverflow.com/questions/35316657/how-to-search-multiple-strings-in-a-string/35324537
Regards,
To search multiple values in a string, you need to custom a function like:
Text.ContainsAny = (string as text, list as list) as logical =>
List.AnyTrue(List.Transform(list, (substring) => Text.Contains(string, substring)))
See a similar thread below:
https://stackoverflow.com/questions/35316657/how-to-search-multiple-strings-in-a-string/35324537
Regards,
Thanks, it works great on a 400 rows dataset I tried.
Hopefully, I can optimize my filters prior to launching the function, because it currently doesn't work on a million+ rows dataset I'm targeting.
Which I find odd, since I only tested for 2 keywords, which I would have expected to take a similar amount of time to when using:
each Text.Contains([Column 1], "Keyword 1") or Text.Contains([Column 1], "Keyword 2")
The code is at risk of leaving residual errors if one of the text value is null, so I added a "try" when filtering.
Text.ContainsAny = (string as text, list as list) as logical =>
List.AnyTrue(List.Transform(list, (substring) => Text.Contains(string, substring))),
#"Filtered Rows" = Table.SelectRows(#"lowercased dataset", each try Text.ContainsAny([Nom du produit], Keywords) otherwise false)