Forum Discussion

Nadim1's avatar
Nadim1
Frequent Visitor
3 years ago
Solved

Can Text.Contains check for a partial match?

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 "AB...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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"

      

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Nadim1
Frequent Visitor
3 years ago

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