Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Vijay_A_Verma
Super User
‎05-06-2022 12:21 AM

Text.ContainsAny and Text.ContainsAll functions in Power Query Language M

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

TextContains.png

TextContains1.png

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"

 

 

 

Comments