powerquerym
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:
I've tried multiple options, but unfortunately can't get desired result. What would be the proper M code?
Solution
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"
