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.

DEPT	CLASS	VENDOR	INV	SALES	PURCH	RTV	UNITAD	PRCCHG	ALLOW
1	136	1122	713	541	807	944	763	931	651
1	136	1122	695	971	508	660	579	907	848
1	136	1313	766	977	739	503	536	795	971
1	136	1313	856	558	758	684	507	981	702
2	210	2247	534	779	874	595	959	828	812
2	210	2247	948	722	685	762	681	623	586
2	210	2888	922	927	974	574	806	897	910
2	210	2888	764	618	612	661	825	780	633

<tbody>
</tbody>

 

[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?