Power Query Group by and Sum

[DanleyL]

I've had a few instances where I am grouping by 4 or 5 columns but then need to sum maybe 12 to 15 columns. Is there a way to accomplish this without having to enter the sum fields one at a time thru all sum columns?

 

[Ron Coderre]

Can you post some sample data and the results you want to see, based on that sample data?

 

[DanleyL]

Can you post some sample data and the results you want to see, based on that sample data?

Hi Ron. See below sample data. I am grouping by Dept/Class/Vendor. Then I will sum the remaining columns. So I highlight the first 3 columns and select "Group By". The grouping automatically shows completed. Then below that I have to enter each field to sum one at a time. Lets say below data has 35 columns to sum. I am now entering those one at a time in the group by function. The question is, is there a way to accomplish this without having to enter each sum field one at a time? I can get the results I want. Just looking for a time saver. Thanks for your time.

[TABLE="width: 640"]
<tbody>[TR]
[TD="class: xl65, width: 64"]DEPT[/TD]
[TD="class: xl65, width: 64"]CLASS[/TD]
[TD="class: xl65, width: 64"]VENDOR[/TD]
[TD="class: xl66, width: 64"]INV[/TD]
[TD="class: xl66, width: 64"]SALES[/TD]
[TD="class: xl66, width: 64"]PURCH[/TD]
[TD="class: xl66, width: 64"]RTV[/TD]
[TD="class: xl66, width: 64"]UNITAD[/TD]
[TD="class: xl66, width: 64"]PRCCHG[/TD]
[TD="class: xl66, width: 64"]ALLOW[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]136[/TD]
[TD="class: xl67"]1122[/TD]
[TD="class: xl67"]713[/TD]
[TD="class: xl67"]541[/TD]
[TD="class: xl67"]807[/TD]
[TD="class: xl67"]944[/TD]
[TD="class: xl67"]763[/TD]
[TD="class: xl67"]931[/TD]
[TD="class: xl67"]651[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]136[/TD]
[TD="class: xl67"]1122[/TD]
[TD="class: xl67"]695[/TD]
[TD="class: xl67"]971[/TD]
[TD="class: xl67"]508[/TD]
[TD="class: xl67"]660[/TD]
[TD="class: xl67"]579[/TD]
[TD="class: xl67"]907[/TD]
[TD="class: xl67"]848[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]136[/TD]
[TD="class: xl67"]1313[/TD]
[TD="class: xl67"]766[/TD]
[TD="class: xl67"]977[/TD]
[TD="class: xl67"]739[/TD]
[TD="class: xl67"]503[/TD]
[TD="class: xl67"]536[/TD]
[TD="class: xl67"]795[/TD]
[TD="class: xl67"]971[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]136[/TD]
[TD="class: xl67"]1313[/TD]
[TD="class: xl67"]856[/TD]
[TD="class: xl67"]558[/TD]
[TD="class: xl67"]758[/TD]
[TD="class: xl67"]684[/TD]
[TD="class: xl67"]507[/TD]
[TD="class: xl67"]981[/TD]
[TD="class: xl67"]702[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl67"]210[/TD]
[TD="class: xl67"]2247[/TD]
[TD="class: xl67"]534[/TD]
[TD="class: xl67"]779[/TD]
[TD="class: xl67"]874[/TD]
[TD="class: xl67"]595[/TD]
[TD="class: xl67"]959[/TD]
[TD="class: xl67"]828[/TD]
[TD="class: xl67"]812[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl67"]210[/TD]
[TD="class: xl67"]2247[/TD]
[TD="class: xl67"]948[/TD]
[TD="class: xl67"]722[/TD]
[TD="class: xl67"]685[/TD]
[TD="class: xl67"]762[/TD]
[TD="class: xl67"]681[/TD]
[TD="class: xl67"]623[/TD]
[TD="class: xl67"]586[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl67"]210[/TD]
[TD="class: xl67"]2888[/TD]
[TD="class: xl67"]922[/TD]
[TD="class: xl67"]927[/TD]
[TD="class: xl67"]974[/TD]
[TD="class: xl67"]574[/TD]
[TD="class: xl67"]806[/TD]
[TD="class: xl67"]897[/TD]
[TD="class: xl67"]910[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl67"]210[/TD]
[TD="class: xl67"]2888[/TD]
[TD="class: xl67"]764[/TD]
[TD="class: xl67"]618[/TD]
[TD="class: xl67"]612[/TD]
[TD="class: xl67"]661[/TD]
[TD="class: xl67"]825[/TD]
[TD="class: xl67"]780[/TD]
[TD="class: xl67"]633[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ron Coderre]

I put your sample data in an Excel Table named Table1 and connected it to Power Query.
In the query editor:
- Select the first 3 columns...Transform.Unpivot.Unpivot_Other_Columns
- Select the first 3 columns...Group by...Sum...Value

This is the M-Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
       {"DEPT", Int64.Type}, 
       {"CLASS", Int64.Type}, 
       {"VENDOR", Int64.Type}, 
       {"INV", Int64.Type}, 
       {"SALES", Int64.Type}, 
       {"PURCH", Int64.Type}, 
       {"RTV", Int64.Type}, 
       {"UNITAD", Int64.Type}, 
       {"PRCCHG", Int64.Type}, 
       {"ALLOW", Int64.Type}}),

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", 
       {"DEPT", "CLASS", "VENDOR"}, 
       "Attribute", 
       "Value"),

    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", 
       {"DEPT", "CLASS", "VENDOR"}, 
       {{"Totals", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

These are the results:

DEPT    CLASS     VENDOR     Totals
1       136       1122       10518
1       136       1313       10333
2       210       2247       10388
2       210       2888       10903

Is that something you can work with?