Can Text.Contains check for a partial match?

[Nadim1]

Hi all,

I'm trying to use the Text.Contains function to check for a partial match (which I believe it should from what I understand). In my example, I have a Name column, and I want to check for "ABC" in that column. For example, there is "Microsoft", "Microsoft Inc." "ABC Inc. (Microsoft)". 

In Column Y, I have each if Text.Contains("Microsoft",[Name]) then perform x. 

However it's only working on rows that have "Microsoft" and not any other variation (it's not returning my result if "Microsoft" is in the sub-string. Is there another function to use, or am I missing something? 

[Daryl-Lynch-Bzy · Accepted solution]

Hi @Nadim1  - I am sorry, but you have made a small mistake in your Text.Contain function.  You need to swap the order of "Microsoft" and [Name].  The [Name] column needs to be first.  Please consider the following example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s1MLsovzk8rUYrVQeIpeOYlg0UcnZxBbAUNuJQmWDwXoS8WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Text Contains", each Text.Contains([Text] , "Microsoft"), type logical),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Text No Case", each Text.Contains( [Text] , "microsoft" , Comparer.OrdinalIgnoreCase ), type logical)
in
    #"Added Custom1"

[Nadim1]

Wow, thank you so much - this works!

When I was reading the documentation - it looks like the syntax is: Text.Contains(text as nullable text, substring as text, optional comparer as nullable function) as nullable logical

Text.Contains - PowerQuery M | Microsoft Learn

this is what I based my formulation off of.. although it looks like in this case it needs to be inverted? Your solution worked, although just wondering if you know what I might've missed here for future reference!

[Daryl-Lynch-Bzy]

Thanks @Nadim1 - i think the key here is that the search substring is "Microsoft" so it need to appears second, and the column you are searching is the "Text".   However, I can understand the confusion because the equivalent excel function asks for the search substring in the first position, and there are other Power Query functions like List.Contains has the search items in first position, then the value searched second.

[Nadim1]

Okay perfect, thank you for the clarification. Yes, probably also getting mixed up with the equivalent Excel Function 🙂