Forum Discussion

Cday21's avatar
Cday21
Frequent Visitor
4 years ago
Solved

Can you add a Grand Total in Power Query

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]

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    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

     

     

5 Replies

  • JedTeres's avatar
    JedTeres
    New Member
    3 years ago

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

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    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's avatar
      Cday21
      Frequent Visitor
      4 years ago

      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's avatar
        ronrsnfld
        Super User
        4 years ago

        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

         

         

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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.