Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
Solved! Go to 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
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...
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.
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?)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |