PQ - new T/F column based on criteria

Power Query
1

Hi,
GL Scenario test.pbix (33.3 KB)

In PQ, I am trying to create a True/False column based on the following criteria:

  1. ID Type contains X

or

  1. ID Type contains “both” X and Z

Any suggestions would be appreciated.

Example of what i am trying to achieve is below and file is attached

image
image403×925 7.59 KB

2

Hi @KimC,

Give this a go, just paste the script into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdO9DoMgEAfwd2F2Ob7UkZZqByexidH4/q/RptIE+Z8pgwPLL5fjPmBdBUklKuE+p3NDeIit+tmNsTtjnrHr8mljY75pfF1CTySPFIqiOqS+qIgZaUmJiCIl4yk0KWV+A0N0RgtSvk4Jq1MK135ugTHP2PHJaK3z8naakdwfMsbA3Ky12Gtdw5SaJm4+CfvaxFjPWPYT2vbQ1vYG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, #"T/F" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"T/F", type logical}}),
    Solution = 
        Table.ExpandListColumn( 
            Table.Group( ChType, {"ID"}, 
                {
                    {"Type", each _[Type]}, 
                    {"contains both X and Z", each List.ContainsAll( _[Type], { "X", "Z" }, Comparer.OrdinalIgnoreCase )}, 
                    {"contains X", each List.ContainsAny( _[Type], { "X" }, Comparer.OrdinalIgnoreCase )}
                }
            ), "Type"
        )
    in
        Solution

Please note there are some differences to the depicted results, for example
ID 122 should return true for all rows
ID 177 only returns true for contains “X”
ID 199 returns false on both because it only contains “Z”

I hope this is helpful

5 Likes
3

Thank you very much Melisa