CODEWITHSUNDEEP
powerquery
user13940869
user13940869

Reputation:

Power Query - How to add total rows for multiple column?

I am looking for a solution in Power Query in Office 365.

I have a table with several columns and >30K rows.

One of the text based columns is the "key" column.

Short Description of what i want to achieve is:

I need to group by the "key" column and aggregate over the years.

And finally keep only the aggregated rows.

Following is the Input: 

Col1    Key 2016    2017    2018    2019    2020    col2    col3    amount
text1   Cat 15  20  15  20  10  text1   text1   500
text2   Cat 25  10      5   20  text2   text2   400
text3   Cat 5   15  5   20  25  text3   text3   200
text4   Dog 5   25  10  5   5   text4   text4   300
text5   Dog 5       25  25  15  text5   text5   200
text6   Bird    25  15  5   5   5   text6   text6   600

And this is what I am looking for as result:

Col1    Key 2016    2017    2018    2019    2020    col2    col3    amount
text1   Cat 45  45  20  45  55  text1   text1   500
text4   Dog 10  35  25  30  20  text4   text4   300
text6   Bird    25  15  5   5   5   text6   text6   600

Here is the a visual description what I am trying to do: enter image description here

I am happy for any help.

Thanks! Aykut

Upvotes: 0

Views: 408

Answers (1)

horseyride
horseyride

Reputation: 21363

This looks like it should work for you

It assumes existence of column titles that are years (2018, 2020, 2025, etc) so it can parse those columns out. It takes any number of columns in any order as input

enter image description here

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
LD=List.Difference(Table.ColumnNames(Source),List.Transform({1980..2050}, each Text.From(_)) ),  // look for columns titles that are not years between 1980 and 2050
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, LD, "Attribute", "Value"),
#"Grouped Rows1" = Table.Group(#"Unpivoted Other Columns", {"Key", "Attribute"}, {{"Value", each List.Sum([Value]), type number}}),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Key"}, {{"Data", each Table.FirstN(Table.Sort(_,{{"amount", Order.Descending}}),1)}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data",List.Difference(LD,{"Key"}), List.Difference(LD,{"Key"})),
#"Merged Queries" = Table.NestedJoin(#"Expanded Data",{"Key"},#"Grouped Rows1",{"Key"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Attribute", "Value"}, {"Attribute", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Table2", List.Distinct(#"Expanded Table2"[Attribute]), "Attribute", "Value"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",Table.ColumnNames(Source))
in #"Reordered Columns"

It unpivots all the year columns, then groups and adds the year columns. It then groups again and picks the row with the highest amount for each key. Finally it merges in all the year data from the first group into the second group and pivots it

Upvotes: 0

Related Questions