cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User I
Super User I

How to do the text.containsany in power query?

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: How to do the text.containsany in power query?

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

8 REPLIES 8
Super User I
Super User I

Re: How to do the text.containsany in power query?

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User I
Super User I

Re: How to do the text.containsany in power query?

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

Super User I
Super User I

Re: How to do the text.containsany in power query?

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 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User I
Super User I

Re: How to do the text.containsany in power query?

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

 

sdew.PNG

Super User I
Super User I

Re: How to do the text.containsany in power query?

@danextian super cool method !!!

Super User III
Super User III

Re: How to do the text.containsany in power query?

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.

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 
Super User I
Super User I

Re: How to do the text.containsany in power query?

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 😊






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Super User I
Super User I

Re: How to do the text.containsany in power query?

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. 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors