Forum Discussion
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 "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?
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"
4 Replies
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"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 logicalText.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!
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.
