Text.ContainsAny and Text.ContainsAll functions in…

Use case – Power Query Language M has List.ContainsAny and List.ContainsAll but doesn’t have Text.ContainsAny and Text.ContainsAll. It does have Text.Contains but not Text.ContainsAny and Text.ContainsAll.

Solution – Below are formulas for Text.ContainsAny and Text.ContainsAll where String contains comma separated values which need to be found and Data has text where string needs to be found.

Text.ContainsAny

 

 

= List.AnyTrue(List.Transform(Text.Split([String],","),(x)=>Text.Contains([Data],x)))

 

 

 Text.ContainsAll

 

 

= List.AllTrue(List.Transform(Text.Split([String],","),(x)=>Text.Contains([Data],x)))

 

 

Note – For case insensitvity, Comparer.OrdinalIgnoreCase can be used.

Text.ContainsAny (case insensitive)

 

 

= List.AnyTrue(List.Transform(Text.Split([String],","),(x)=>Text.Contains([Data],x,Comparer.OrdinalIgnoreCase)))

 

 

 Text.ContainsAll (case insensitive)

 

 

= List.AllTrue(List.Transform(Text.Split([String],","),(x)=>Text.Contains([Data],x,Comparer.OrdinalIgnoreCase)))

 

 

Below are results of these formulas

For testing of these formulas – See the working here – Open a blank query – Home – Advanced Editor – Remove everything from there and paste the below code to test 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslIVSgszUzOVkgqyi/PU0jLr1DIKs0tKFbIL0stUigBSuckVlUqpOSnK+kogdUoxeqQqg8kowNWTobmnMz8PB0Qnwy9SfkVOsmJuak5ZOj1Ajk6MNTT2ZsMzT4gR/uAHR0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t, String = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Text.ContainsAny", each List.AnyTrue(List.Transform(Text.Split([String],","),(x)=>Text.Contains([Data],x)))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Text.ContainsAll", each List.AllTrue(List.Transform(Text.Split([String],","),(x)=>Text.Contains([Data],x)))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Any - Case Insensitive", each List.AnyTrue(List.Transform(Text.Split([String],","),(x)=>Text.Contains([Data],x,Comparer.OrdinalIgnoreCase)))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "All - Case Insensitive", each List.AllTrue(List.Transform(Text.Split([String],","),(x)=>Text.Contains([Data],x,Comparer.OrdinalIgnoreCase))))
in
    #"Added Custom3"