The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.