Forum Discussion

artemyp's avatar
artemyp
Frequent Visitor
3 years ago

Power Query Group By/Aggregate based on condition + dynamic columns

I have a table with products (Cols E-F) from 3 countries (Col A), with weekly values (Cols H-L) and condition (Col G) based on which I need to aggregate the weekly values, and also display the aggregation logic. Columns 44-48 are week numbers, hence these should be treated as dynamic columns.

 

Current table:

 

Desired output:

 

I've tried multiple options, but unfortunately can't get desired result. What would be the proper M code?

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    In Power Query, there is an Original table as shown in your image. Duplicate it 2 times and rename those as Table 1 and Table 2.

     

    Copy-paste the below M Query in Advanced Editor in respective tables, i.e., Table 1 and Table 2

    Table 1.

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYpAwoYgAKQDivJTSpNLQExDPWxkrE60khElmo0p0QxiRyJhIxBAaAYxjfSwkTBnk60ZZHMUEjYGAYRmENNYDxsJs5lszcZka44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Producer = _t, Packaging = _t, Brand = _t, #"SAP Code" = _t, #"Material Descriptions" = _t, #"44" = _t, #"45" = _t, #"46" = _t, #"47" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Producer", type text}, {"Packaging", type text}, {"Brand", type text}, {"SAP Code", Int64.Type}, {"Material Descriptions", type text}, {"44", type number}, {"45", type number}, {"46", type number}, {"47", type number}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Producer", "Packaging", "Brand", "SAP Code", "Material Descriptions"}, {{"44", each List.Sum([44]), type nullable number}, {"45", each List.Sum([45]), type nullable number}, {"46", each List.Sum([46]), type nullable number}, {"47", each List.Sum([47]), type nullable number},
    {"Country", each Text.Combine([Country], "+"), type text}
    }),
        #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Producer] <> "Z"))
    in
        #"Filtered Rows"

     


    Table 2.

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYpAwoYgAKQDivJTSpNLQExDPWxkrE60khElmo0p0QxiRyJhIxBAaAYxjfSwkTBnk60ZZHMUEjYGAYRmENNYDxsJs5lszcZka44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Producer = _t, Packaging = _t, Brand = _t, #"SAP Code" = _t, #"Material Descriptions" = _t, #"44" = _t, #"45" = _t, #"46" = _t, #"47" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", Int64.Type}, {"Producer", type text}, {"Packaging", type text}, {"Brand", type text}, {"SAP Code", Int64.Type}, {"Material Descriptions", type text}, {"44", type number}, {"45", type number}, {"46", type number}, {"47", type number}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Producer] = "Z"))
    in
        #"Filtered Rows"

     

     

    3. Append Table 1 and Table 2

    PS: The below link proved to be helpful while solving your query. 
    Using Group by to Concatenate Text in Power Query - BI Gorilla