CODEWITHSUNDEEP
powerquery
Aaron
Aaron

Reputation: 331

Power Query - Group based on two conditions

I have a simple table with this stucture:

Order Nr    Key Status  XX  YY  ZZ
100 ABC100  A   0   0   0
100 ABC100  B   0   0   0
100 ABC100  C   0   0   0
101 XYZ100  A   0   0   0
101 ABC101  A   0   0   0
102 ABC102  A   0   0   0

Now I would like to group/ transform the table above to this result table:

Order Nr    ABC XYZ Active
100 TRUE    FALSE   FALSE
101 TRUE    TRUE    TRUE
102 TRUE    FALSE   TRUE

The first column shows me the Order-Nr. The second and third column show me if in the column key there is a string that begins with ABC or XYZ. The last column shows me if on Order-Nr level any records exists with Status B or C. If so, then it is true. If not, then it is false. How would you do that in Power Query?

Upvotes: 0

Views: 1712

Answers (1)

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

As an option:

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    g = (y,z) => List.AnyTrue(List.Transform(y,z)),
    f = (x)=>{x, each g([Key], each Text.StartsWith(_,x))},
    group = Table.Group(Source, "Order Nr", {f("ABC"), f("XYZ"),
            {"Active", each g([Status], each List.Contains({"B","C"},_))}})
in
    group

enter image description here

UPD:

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    f = (x)=>{x, each List.AnyTrue(List.Transform([Key], each Text.StartsWith(_, x)))},
    group = Table.Group(Source, "Order Nr", {f("ABC"), f("XYZ"),
            {"Active", each List.AllTrue(List.Transform([Status],
                                        each not List.Contains({"B","C"},_)))}})
in
    group

enter image description here

Upvotes: 2

Related Questions