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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
danextian
Super User
Super User

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

12 REPLIES 12
ImkeF
Super User
Super User

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
        )
    )
)

 

Imke Feldmann (The BIccountant)

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

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

I love this solution!

smpa01
Super User
Super User

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
danextian
Super User
Super User

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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

@danextian super cool method !!!

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.

 

Imke Feldmann (The BIccountant)

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

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

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

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Anonymous
Not applicable

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 

Hi @Anonymous ,

 

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Anonymous
Not applicable

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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