Inserting Summation Rows in Power Query

[everiwa]

I am using Office 2019 and i want to add a summation row after every last unique ref "GRV" and also the cell below the last unique reference "GRV', i want to sum totals of Columns "Tpt Charge","Offloading Fee" & "Tollgate Fee". How do i do that?

 

[GraH]

You make a second query, referring to this one and do a group by "GRV", doing the sum on those columns. Give your your totals columns the same names. Add a Custom Column with "Index" 1,000,000.
Refer again to the first query, add an Index and append the second one with the totals.
Sort on GRV and Index.

 

[GraH]

[EDIT] Example in 1 Query, same idea, including both total now.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type any}, {"Site", type any}, {"SBU", type any}, {"Order", type any}, {"Tonnes", type number}, {"Tpt Charge", type number}, {"Offloading Fee", type number}, {"Tollgate Fee", type any}, {"GRV", type text}, {"p/order", type any}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Tonnes", "Tpt Charge", "Offloading Fee", "Tollgate Fee"}),
#"Grouped Rows" = Table.Group(#"Replaced Value", {"GRV"}, {{"Tonnes", each List.Sum([Tonnes]), type number}, {"Tpt Charge", each List.Sum([Tpt Charge]), type number}, {"Offloading Fee", each List.Sum([Offloading Fee]), type number}, {"Tollgate Fee", each List.Sum([Tollgate Fee]), type none}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "GRV1", each [Tonnes]+[Tpt Charge]+[Offloading Fee]+[Tollgate Fee]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Index", each 1000000),
#"Grouped Rows1" = Table.Group(#"Added Custom1", {}, {{"Tonnes", each List.Sum([Tonnes]), type number}, {"Tpt Charge", each List.Sum([Tpt Charge]), type number}, {"Offloading Fee", each List.Sum([Offloading Fee]), type number}, {"Tollgate Fee", each List.Sum([Tollgate Fee]), type number}, {"GRV1", each List.Sum([GRV1]), type number}, {"Index", each List.Sum([Index]), type number}}),
Custom1 = #"Changed Type",
#"Added Index" = Table.AddIndexColumn(Custom1, "Index", 1, 1),
#"Added Custom2" = Table.AddColumn(#"Added Index", "GRV1", each [GRV]),
Custom2 = Table.Combine({#"Added Custom2",#"Added Custom1",#"Grouped Rows1"}),
#"Replaced Value1" = Table.ReplaceValue(Custom2,null,"ZZZ99999999",Replacer.ReplaceValue,{"GRV"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value1",{{"GRV", Order.Ascending}, {"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"GRV", "p/order", "Index"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"GRV1", "GRV"}})
in
#"Renamed Columns"

 

[shaowu459]

Please try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Custom1 = Table.Combine(Table.Group(Source,"GRV",{"n",each let c=Table.ToColumns(_) in #table(Table.ColumnNames(_),Table.ToRows(_)&{{null,null,null,null}&Record.ToList([m=List.Sum(c{4}),n=List.Sum(c{5}),o=List.Sum(c{6}),p=List.Sum(c{7}),q=m+n+o+p,r=null])})})[n])
in
    Custom1

供参考.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Despath Date	Site	SBU	Order Number	Tonnes	Tpt Charge	Offloading Fee	Tollgate Fee	GRV	p/order
2					45	38	74	5	Q1	16759
3					94	8	76	72	Q1	16759
4					84	35	54	62	Q1	16759
5					79	32	42	78	Q2	16759
6					99	33	43	98	Q2	16760
7					86	60	27	49	Q2	16760
8					40	12	49	78	Q2	16760
9					62	34	30	2	Q2	16763
10					93	75	33	69	Q3	16763
11					50	39	27	37	Q3	16763
12	Despath Date	Site	SBU	Order Number	Tonnes	Tpt Charge	Offloading Fee	Tollgate Fee	GRV	p/order
13					45	38	74	5	Q1	16759
14					94	8	76	72	Q1	16759
15					84	35	54	62	Q1	16759
16					223	81	204	139	647
17					79	32	42	78	Q2	16759
18					99	33	43	98	Q2	16760
19					86	60	27	49	Q2	16760
20					40	12	49	78	Q2	16760
21					62	34	30	2	Q2	16763
22					366	171	191	305	1033
23					93	75	33	69	Q3	16763
24					50	39	27	37	Q3	16763
25					143	114	60	106	423
Sheet2

 

[GraH]

Rather advanced with the let inside a function and the compressed code for grouping. Impressive. Not sure if less advanced user can adopt this easily.

 

[shaowu459]

Rather advanced with the let inside a function and the compressed code for grouping. Impressive. Not sure if less advanced user can adopt this easily.

Yes, for beginners it is not easy to understand and apply. GUI+M function would be a better way.

 

[Bo_Ry]

Another 1 step M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grouped = Table.Combine(Table.Group(Source,"GRV",{"C",each let a={List.Sum([Tonnes]),List.Sum([Tpt Charge]),List.Sum([Offloading Fee]),List.Sum([Tollgate Fee])} in Table.Combine({_,#table( List.Range(Table.ColumnNames(_),4,5),{a&{List.Sum(a)}})})})[C])
in
    Grouped

 

[GraH]

Also nice one. Though one can argue it is a one line solution. It is nesting functions. According to some experts that is not always better then adding extra steps. Yet as always it depends. Might well be the case these short codes also run way faster. I did not test.
Just shows PQ offers ways that appeal to both non programmers like myself and to professionals having code writing kills like you guys being able to come up with these elegant pieces of work.