cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cday21
New Member

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]

 

1 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

 

 

 

ronrsnfld_0-1638559163331.png

 

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

 

 

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

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:

AlexisOlson_0-1638565346541.png

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
Super User
Super User

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?

@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?)

 

Cday21_0-1638556182402.png

 

 

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

 

 

 

ronrsnfld_0-1638559163331.png

 

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

 

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!