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

[artemyp]

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?

[Anonymous]

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