0

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?

Improve this question
4
  • Is the AGG condition locked in or dynamic (changing each time)? Are the items fixed, or will there sometimes be 'sum of all 5' and 'sum of countries 1+2+5' Commented Dec 28, 2022 at 16:48
  • Currently there only 3 conditions: sum of all, sum of countries 1+2 and separate. I expect there can also be conditions like sum of countries 1+3 , 2+3. But not more. One option i thought of: create separate tables by filtering based on condition -> so there are 3 separate tables -> then Append them. But perhaps that's not the best way to do this. Commented Dec 28, 2022 at 16:50
  • And what does AGG condition apply to? Just SAP code? Commented Dec 28, 2022 at 16:51
  • Basically yes, but Producer, Packaging, Brand and Material description should also remain. Commented Dec 28, 2022 at 16:52

1 Answer 1

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Next time please paste data and not pictures of data
Thank you, works perfect! Comment regarding data/not picture - noted!
Anyone have any idea why the #Reorder of columns needed to get the pivot to work? Never seen that before
There is a note which reads: "During the pivot columns operation, Power Query will sort the table based on the values found on the first column—at the left side of the table—in ascending order." located here: learn.microsoft.com/en-us/power-query/pivot-columns which might explain it. Annoyingly, the function help page doesn't mention it at all.
Crazily enough, the #reorder doesnt change contents of the first column before the pivot, so no idea what that is. Thanks for the input though

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.