CODEWITHSUNDEEP
powerquerym
Artemy Panin
Artemy Panin

Reputation: 67

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:

Country Producer Packaging Brand SAP code Material Description AGG condition 44 45 46 47 48
1 x x x 11111 product 1 sum of all 3 1.1 1.1 1.1 1.1 1.1
2 x x x 11111 product 1 sum of all 3 1.1 1.1 1.1 1.1 1.1
3 x x x 11111 product 1 sum of all 3 1.1 1.1 1.1 1.1 1.1
1 y y y 22222 product 2 sum of countries 1+2 2.2 2.2 2.2 2.2 2.2
2 y y y 22222 product 2 sum of countries 1+2 2.2 2.2 2.2 2.2 2.2
1 z z z 33333 product 3 separate 3.3 3.3 3.3 3.3 3.3
2 z z z 33333 product 3 separate 3.3 3.3 3.3 3.3 3.3
3 z z z 33333 product 3 separate 3.3 3.3 3.3 3.3 3.3

Desired output:

Expected output

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

Upvotes: 0

Views: 506

Answers (1)

horseyride
horseyride

Reputation: 21298

Try

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"AGG condition", "Material Description", "SAP code", "Brand", "Packaging", "Producer", "Country"}, "Attribute", "value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"SAP code", "AGG condition", "Attribute"}, {
    {"total", each List.Sum([value]), type number}, 
    {"Producer", each _[Producer]{0}, type text },
    {"Packaging", each _[Packaging]{0}, type text },
    {"Brand", each _[Brand]{0}, type text },
    {"Material Description", each _[Material Description]{0}, type text},
    {"Alternate Country", each Text.Combine(List.Transform(List.Distinct(_[Country]), each Text.From(_)),"+"),type text},
    {"data", each _, type table}
}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([AGG condition] <> "separate")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"data", "AGG condition"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Alternate Country", "Country"}, {"total", "value"}}),

#"Filtered Rows2" = Table.SelectRows(#"Grouped Rows", each ([AGG condition] = "separate")),
#"Expanded data" = Table.ExpandTableColumn(#"Filtered Rows2", "data", {"Country", "value"}, {"Country", "value"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded data",{"AGG condition", "total", "Alternate Country"}),

combined = Table.Combine({#"Renamed Columns",#"Removed Columns"}),
#"Reordered Columns" = Table.ReorderColumns(combined,{"SAP code", "Country", "Attribute", "value", "Producer", "Packaging", "Brand", "Material Description"}),  // needed, no idea why
#"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Attribute]), "Attribute", "value", List.Sum)
in #"Pivoted Column"

enter image description here

Upvotes: 2

Related Questions