Custom Column - Check if all values are present in a string

[silverdale9999]

I have a text field in my data source which contains a number of XML tags.  As part of the data load, this XML is parsed into columns.

The text field is however editable by users in the source system, so sometimes the XML tags are messed about with, which in turn causes my dataload to fail. 

In order to validate this XML prior to load, I was thinking of checking if all of the 10 tags are present in the string. (So 20 values to check, if the open/close tags are checked).  

What I'm looking for is something along the lines of:

If [notes] contains all 20 of these values, then [notes], else "Bad XML" (The bit in italics is the part I am stuck with!)

I've found examples that use multiple "if Text.Contains" but these don't appear to give me the option of a match against every value.. it looks for any of them.

Thanks in advance 😁

[Jimmy801 · Accepted solution]

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
    Add

Copy 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

[silverdale9999]

Thanks - just tried your example and it looks like it will work!

[AllisonKennedy]

Could the List.ContainsAll work here?

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 { }

[silverdale9999]

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?

[Anonymous]

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