# Power Query to Add Subtotal & Grand Total Row



## legalhustler (Aug 7, 2021)

StationAmountUnitsCost5102.705204.70*5 Total**30**6**.70*629.70*6 Total**2**9**.70*753.707107.70*7 Total**15**10**.70**Grand Total**47**25**.70*


----------



## legalhustler (Aug 7, 2021)

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 (Aug 9, 2021)

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


----------



## legalhustler (Aug 9, 2021)

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 (Aug 9, 2021)

Alex Blakenburg said:


> 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 (Aug 10, 2021)

@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 (Aug 10, 2021)

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
```


----------

