How to do the text.containsany in power query?

[AiolosZhao]

Hi all,

I didn't find the text.containsany function in power query, and I don't find a way to make that, do you have any method about it?

Example:

Column name is Name and 3 rows

Name

Research

Army

Hospital

and what I want is to search the Name column value, if the value include Res or Arm, then true else false.

I don't want to do it using text.contains twice in power query, because in my actually data, the search string has more than 10 values.

So do you have any method to do that?

Thanks.

Aiolos Zhao

[ImkeF]

Hey Overhear,

it's like danextian mentioned, you must use Comparer.OrdinalIgnoreCase instead:

List.AnyTrue(
    List.Transform(
        {"Res", "Arm"}, 
        (listItem) => Text.Contains( 
            [Name], 
            listItem,
            Comparer.OrdinalIgnoreCase
        )
    )
)

[KarenXu]

I love this solution!

[smpa01]

@AiolosZhao  for a big table the peformance will take a hit but this is another way

let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Desktop/How-to-do-the-text-containsany-in-power-query/m-p/932611"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(7) > TR > :nth-child(1), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(7) > * > TR > :nth-child(1)"}}, [RowSelector="DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(7) > TR, DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(7) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Added Custom1" = Table.AddColumn(#"Promoted Headers", "Keywords", each {"Res","Arm"}),
    #"Expanded Keywords" = Table.ExpandListColumn(#"Added Custom1", "Keywords"),
    #"Grouped Rows" = Table.Group(#"Expanded Keywords", {"Name"}, {{"ad", each _, type table [Name=text, Keywords=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
X=[ad],
Y = Table.AddColumn(X, "Custom", each Text.Contains([Name],[Keywords]))
in Y),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Keywords", "Custom"}, {"Name", "Keywords", "Custom.1"})
in
    #"Expanded Custom"

[danextian · Accepted solution]

Hello,

Try this in Power Query as custom column:

let 
  SearchString = {"res", "arm"}
in
  List.Count(Splitter.SplitTextByAnyDelimiter(SearchString)  (Text.Lower([Data]))) >= 1

Replace the contents of {" "} with your actual search strings and [Data] with your actual column data.

[beerderailleur]

Hello! How can I get this to work if my values have identical first words. E.g. I have{ "blue umbrella" , "blue hat", "blue scarf"} I only want the result to  give me blue hat but it seems that the logic is yielding to "true" for all those with blue

[smpa01]

@danextian super cool method !!!

[ImkeF]

Hi @danextian ,

nice code, I think it should be > 1 instead of >=1 at the end of your formula.

My take would be to add a column with this formula:

List.AnyTrue(
    List.Transform(
        {"Res", "Arm"}, 
        (listItem) => Text.Contains( 
            [Name], 
            listItem
        )
    )
)

Where the optional 3rd argument of the Text.Contains-function could hold "Ordinal.IgnoreCase" if a case insensitive match would be required.
This doesn't require any transformation of the original colum value. But not sure if this actually makes a difference in performance at the end.

[Overhear]

Thanks for providing this answer, @ImkeF. Would you mind providing an example that includes the Ordinal.IgnoreCase specification?

[danextian]

hi @ImkeF ,

 I wanted to List.Select to the code but it slipped my mind. So, in the formula I posted, should have been >1 instead of >=1.  

That is also a pretty nice code and I like how the lines are indented. By the way, I think it should be Comparer.OrdinalIgnoreCase 😊

[AiolosZhao]

Hi @danextian ,

Thanks a lot! That's really cool method.

Could you please help to explain the expression?

It's my first time to use function in a function,

so you create a function using the 

Splitter.SplitTextByAnyDelimiter

But you change the parameter to a list

then 

(Text.Lower([Data]))

 the function works in the above part and returns a list

finally, you count the list

am I right?

Aiolos Zhao 

[danextian]

Hi @AiolosZhao ,

The function splits a text string based on a defined list of delimiters. "Research is split into "" and "earch" and are returned as a list. If none of the delimiters is in the string, the function still returns a list but just a single row containing the original text string. So if List.Count > 1, the cell contains the string being searched. 

[AiolosZhao]

any solution about that? Or do I need to more clarify my questions? Aiolos Zhao