Forum Discussion
Custom Column - Check if all values are present in a string
Hello silverdale9999
here a good solution. Use a combination of List.AllTrue, List.Transform and Text.Contains. The step TagsToBeCheck hold the list with all your tags to be search. This is list is then transformed by checking every list content if its present in your text string
Check out the solution
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVijJLMlJVUhOSVUoyVfITVXILFYoyUgsUSjILy7OTMpJVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tag = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tag", type text}}), TagsToBeCheck= {"me", "that", "possible"}, Add = Table.AddColumn ( #"Changed Type", "Check", (add)=> List.AllTrue(List.Transform(TagsToBeCheck, each Text.Contains(Text.Upper(add[Tag]), Text.Upper(_)))) ) in AddCopy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
https://docs.microsoft.com/en-us/powerquery-m/list-containsall
You may need to trick Power Query into thinking your text field is a list, which you can usually do with { }
Thanks for the quick reply. (I should have spotted "ContainsAll" when i was looking at the List help on the powerquery page earlier!)
I've tried creating a test custom column using 2 of the XML tags as follows, using the { } around the Notes field name, but all the logical values in the custom column are returning as "false"
= Table.AddColumn(#"Added Custom", "Custom", each List.ContainsAll({[Notes]}, {"<Title>", "</Title>"}))
Am I missing something really obvious?
to get a usufull answer you should provide usufull input and expected output (may be in a format that is easy to copy).
for what I can imagine, you could try this modification at your code:
= Table.AddColumn(#"Added Custom", "Custom", each List.ContainsAll( Text.Split( [Notes], " "), {"<Title>", "</Title>"}))
but is just a guess in the dark