Can you add a Grand Total in Power Query

[Cday21]

Hi All,

I have Table that has been filtered to only show the data I need.  However, is it possible to show the Grand Total Row to the Table, so that it is present when I merge it with anther Table? (or Possibly total each row once Merged?)

I googled, and found a Power Query Total blog,

let
    Source = Excel.Workbook(File.Contents("Confidential"), null, true),
    Gateways1 = Source{[Name="Gateways"]}[Data],
    #"Removed Top Rows" = Table.Skip(Gateways1,3),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([#"Division #"] = "660" or [#"Division #"] = "703" or [#"Division #"] = "706")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Scanned Movement", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Year/Period"}, {{"LA Gateway", each List.Sum([Scanned Movement]), type nullable number}}),
    Sales = Table.Column(#"Grouped Rows", "LA Gateway"),
    GrandTotal = List.Sum(Sales),
    TableWithShare = Table.AddColumn(#"Grouped Rows", "Share", {GrandTotal})
in
    TableWithShare

but the last step TableWithShare throws an error:

Expression.Error: We cannot convert a value of type List to type Function.
Details:
    Value=[List]
    Type=[Type]

[JedTeres]

I used the GUI and did the following:

1. added a new custom column with the value "1." Nothing special about 1, just wanted a constant value

2. grouped by my new column and created a count (for my purposes I wanted a count, but it could've been a sum also) 

3. Appended that row to my other query

Nice to see it written out in M code but I wanted to share a beginner-friendly approach for others who stumble onto this thread...

[AlexisOlson]

I'd do this by grouping the table and appending that to the original table like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlQIcFEwMFTSUTI1MTQ2MgMyDC0tjIzNjZVidZBUGAElTMwtjcwscakwBqmwMDC3MMWlwgRshoGlpTGyilgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Year/Period" = _t, #"LA Gateway" = _t, Share = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year/Period", type text}, {"LA Gateway", Int64.Type}, {"Share", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {}, {{"Year/Period", each "Grand Total", type text}, {"LA Gateway", each List.Sum([LA Gateway]), type number}, {"Share", each List.Max([Share]), type number}}),
    #"Appended Query" = Table.Combine({#"Changed Type", #"Grouped Rows"})
in
    #"Appended Query"

This can be done almost entirely with GUI where the exceptions are changing the grouping definition of Year/Period and the step reference when appending queries.

1. Do the Group By like this:

2. Replace List.Max([Year/Period]) with "Grand Total" in the generated step.

3. Click Append Queries and choose to append the current table with itself.

4. Replace the first #"Grouped Rows" in the generated step with #"Changed Type".

This method extends nicely to calculating subtotals. For subtotals, you'd group over certain columns rather than grouping the entire table over no specific column.

[HotChilli]

GrandTotal is just a number so remove the curly brackets and add 'each' before it.

Was it like this on the blog you got the code from?

[Cday21]

@HotChilli No, the blog didn't have the curly bracets.  That was from me playing around to figure out a solution. Here's the link

I added 'each' before grand total, but it added this as a new column.  Is there a way to add a grand total to the end of the table? (In this example, Row 5?)

[ronrsnfld · Accepted solution]

Not sure how to fit this in with the rest of your query, but to add a GT row to the table you show in your most recent response, you just need to construct the row, then add it to the table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlQIcFEwMFTSUTI1MTQ2MlOK1UESNwKKm5hbGplZooobg8QtDMwtTFHFTcDqDSwtjZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Year/Period" = _t, #"LA Gateway" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year/Period", type text}, {"LA Gateway", Int64.Type}}),

//create grand total row
    gt = [#"Year/Period"="Grand Total", LA Gateway = List.Sum(#"Changed Type"[LA Gateway])],

//Add the row to the bottom of the table
    withGT=Table.FromRecords(Table.ToRecords(#"Changed Type") & {gt})
in
    withGT

You could certainly create a function using this algorithm, for example, and use it to add subtotals for different groups -- perhaps feeding it the Table and the column(s) to total as a variable