Power Query to Add Subtotal & Grand Total Row

[legalhustler]

Station	Amount	Units	Cost
5	10	2	.70
5	20	4	.70
5 Total	30	6	.70
6	2	9	.70
6 Total	2	9	.70
7	5	3	.70
7	10	7	.70
7 Total	15	10	.70
Grand Total	47	25	.70

 

[legalhustler]

Hello All,

I'm looking for a power query solution to add subtotal row and a grand total row like the above. Note the last column the value stays the same. Prefer using the Power Query interface for the solution if possible else a custom code will suffice. Thanks!

 

[Alex Blakenburg]

Why not close and load Power Query as a pivot table ?

 

[legalhustler]

I found the following which shows different codes but not sure how to add the Grand Total row. Anyone?

How to add subtotal in a row in Power Query

social.technet.microsoft.com

 

[legalhustler]

Why not close and load Power Query as a pivot table ?

Yeah I know I could probably use that but I have a lot of power query tables that gets loaded directly so wanted to avoid creating any pivot tables. Besides, not sure if pivot tables can get me that last column with the same subtotal and grand total amount

 

[billszysz]

@legalhustler, try this below

let
  Source = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Station", Int64.Type}, {"Amount", Int64.Type}, {"Units", Int64.Type}, {"Cost", type number}}
  ), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"Station"}, 
    {
      {"tbl", each _, type table}, 
      {"Amount", each List.Sum([Amount]), type nullable number}, 
      {"Units", each List.Sum([Units]), type nullable number}, 
      {"Cost", each List.First([Cost]), type nullable number}
    }
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Grouped Rows", 
    "Tables", 
    each Table.Combine(
      {
        [tbl], 
        Table.FromRecords(
          {Record.RemoveFields(_, "tbl") & [Station = Text.From([Station]) & " Total"]}
        )
      }
    )
  ), 
  Grand = Table.Group(
    #"Changed Type", 
    {}, 
    {
      {"Station", each "Grand Total", type nullable text}, 
      {"Amount", each List.Sum([Amount]), type nullable number}, 
      {"Units", each List.Sum([Units]), type nullable number}, 
      {"Cost", each List.First([Cost]), type nullable number}
    }
  ), 
  Result = Table.Combine(#"Added Custom"[Tables] & {Grand})
in
  Result

 

[Bo_Ry]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grouped = Table.Group(Source, {"Station"}, {{"T", each  Table.InsertRows(_,Table.RowCount(_),{[Station= Text.From([Station]{0})&" Total",Amount= List.Sum([Amount]),Units=List.Sum([Units]),Cost=List.Average([Cost])]})}}),
    Combine = Table.Combine(Grouped[T]),
    GrandTotal = Table.InsertRows(Combine,Table.RowCount(Combine),{[Station= "Grand Total",Amount= List.Sum(Source[Amount]),Units=List.Sum(Source[Units]),Cost=List.Average(Source[Cost])]})
in
    GrandTotal