Forum Discussion
Can Text.Contains check for a partial match?
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"
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.
Okay perfect, thank you for the clarification. Yes, probably also getting mixed up with the equivalent Excel Function 🙂
