Group By 5 columns, sum remaining 25 columns?

Nick Burns

Nick Burns

Member
Joined
May 24, 2017
Messages
162
Reaction score
0
Points
16
Excel Version(s)
Office 365
I have 30 columns. I'm only grouping by 5 columns
What's the easiest why of summing the remaining 25 columns without having to input them all in the UI?
 
perhaps uploading a sample worksheet would help us to understand specifically what you want.
 
Groups the first 5 columns and sums all the other columns from Table1

Code: 
let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   Columns = List.FirstN(Table.ColumnNames(Source),5),
  Unpivot = Table.UnpivotOtherColumns(Source, Columns, "Attribute", "Value"),
  Group = Table.Group(Unpivot, List.Combine({Columns, {"Attribute"} }), {{"sum", each List.Sum([Value]), type number}}),
  Pivot = Table.Pivot(Group, List.Distinct(Group[Attribute]), "Attribute", "sum")
in Pivot
 
Still working on getting a sample file.
I've seen the Unpivot/Pivot pattern before, but have run into memory issues when dealing with large datasets (this one would effectively multiply the number of rows by 25).

Here's what the GroupBy step looks like using the UI:
Code: 
= Table.Group(PreviousStep, {"kno", "kcheck", "tce_date", "pec_analysis", "cpg-method"}, {{"tce_reimb", each List.Sum([tce_reimb]), type number}, {"tce_rhrs", each List.Sum([tce_rhrs]), type number},
{"tce_rday__1", each List.Sum([tce_rday__1]), type number}, {"tce_rday__2", each List.Sum([tce_rday__2]), type number}, {"tce_rday__3", each List.Sum([tce_rday__3]), type number}, 
{"tce_rday__4", each List.Sum([tce_rday__4]), type number}, {"tce_rday__5", each List.Sum([tce_rday__5]), type number}, {"tce_rday__6", each List.Sum([tce_rday__6]), type number}, 
{"tce_rday__7", each List.Sum([tce_rday__7]), type number}, {"tce_ohrs", each List.Sum([tce_ohrs]), type number}, {"tce_oday__1", each List.Sum([tce_oday__1]), type number}, 
{"tce_oday__2", each List.Sum([tce_oday__2]), type number}, {"tce_oday__3", each List.Sum([tce_oday__3]), type number}, {"tce_oday__4", each List.Sum([tce_oday__4]), type number}, 
{"tce_oday__5", each List.Sum([tce_oday__5]), type number}, {"tce_oday__6", each List.Sum([tce_oday__6]), type number}, {"tce_oday__7", each List.Sum([tce_oday__7]), type number}, 
{"tce_dhrs", each List.Sum([tce_dhrs]), type number}, {"tce_dday__1", each List.Sum([tce_dday__1]), type number}, {"tce_dday__2", each List.Sum([tce_dday__2]), type number}, 
{"tce_dday__3", each List.Sum([tce_dday__3]), type number}, {"tce_dday__4", each List.Sum([tce_dday__4]), type number}, {"tce_dday__5", each List.Sum([tce_dday__5]), type number}, 
{"tce_dday__6", each List.Sum([tce_dday__6]), type number}, {"tce_dday__7", each List.Sum([tce_dday__7]), type number}})

I was looking for some type of inline function that would take the list of "group by columns" and sum all others. (similar to the unpivot/pivot pattern)
 
This is a dynamic way that doesn't require the unpivoting and pivot-back:

Code: 
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIGYkMDIGFqoBSrgxAzAomZQcScgEwXIDYGiZkjxFyB2AQkZgEUiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [GroupColA = _t, GroupColB = _t, SumCol1 = _t, SumCol2 = _t]),
    MySource = Table.TransformColumnTypes(Source,{{"GroupColA", type text}, {"GroupColB", type text}, {"SumCol1", Int64.Type}, {"SumCol2", Int64.Type}}),
    GroupColumns = {"GroupColA", "GroupColB"},
    AggregateColumns = List.Difference(Table.ColumnNames(MySource),GroupColumns),
    DynamicGrouping = List.Transform([COLOR=#222222][FONT=Verdana]AggregateColumns[/FONT][/COLOR], (ColName) => {ColName, (Partition)=> List.Sum(Table.Column(Partition,ColName))}),
    #"Grouped Rows" = Table.Group(MySource, GroupColumns, DynamicGrouping)
in
    #"Grouped Rows"

Just paste the code into the advanced editor and follow the steps. Then connect to your data (step MySource) and adjust the group column in step "GroupColumns". The rest should happen automagically .
 
Imke - perfect! Works like a charm - I even found where I needed to modify it to maintain the number type.
 
Great! Good to hear its fast.
Wanted to make it a blogpost for a while already, should do that soon..

And yes, I forgot to include the types but have adjusted that in the code in GitHub meanwhile :)
 
Is this code no longer on GitHub?
 
You must log in or register to reply here.
Back
Top