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

     

     

HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity 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
    Icon for Super User rankSuper 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