Forum Discussion
How to do a running Sum by group in Power Query?
You can use this query (assuming you want to group on "BU"):
let Source = Table1, TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)), #"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", fnAddRunningSum, TableType}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Location", "Month", "Cost", "Running Sum"}, {"Location", "Month", "Cost", "Running Sum"}) in #"Expanded AllData"With function fnAddRunningSum:
(MyTable as table) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Source[Cost],{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
AddedRunningSum
Hi SegerC
Sorry for the delay, I had quite an intense week due to COVID impact on the business I am working for.
The error comes up in the Expand step as PBI is lazy and does not calculate anything until it "has to". In this case, the function calls to AddedRunningSum happen on Expand not on Group step.
The "in" bit of each query or function usually refers to the last step in the sequence, not to the name of the query.
Please change you code to this:
(MyTable as table, MyColumn as text) =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Cumul", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Table.Column(Source,MyColumn),{0},(cumulative,MyColumn) => cumulative & {List.Last(cumulative) + MyColumn})),
Cumul = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
Cumul // not AddedRunningSum!
Everything else in your code seems to be Ok, I think it should work once you fix the function.
If it still complains on TableType, try removing it from the "Table.GroupBy" step and see if the code works.
Kind regards,
John
@SegerC if you haven't yet managed to make it work, this might help.
This was the final code I used, which worked great. I added a couple of variables for you to make it explicitly clear what you need to change.
You'll need to change three things in Code Sample 1:
1) Source
2) ColumnToGroup
3) ColumnToSum
The result will be your original grouping column, plus a column with all the table type data ready to be expanded.
Code Sample 1: The original table, to which I want to add a grouped running total:
let
Source = #"SourceDataInput", // << - This step can be replaced by a source table or your previous steps.
ColumnToGroup = "ColumnNameToGroup", // << - Change this to the column name that you want to group by.
ColumnToSum = "ColumnNameToSum", // << - Change this to the column that you want a running total of.
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
#"Grouped Running Totals" = Table.Group(Source, {ColumnToGroup}, {{"AllData", each fn_AddCol_RunningSum(_,ColumnToSum,ColumnToGroup), TableType}})
in
#"Grouped Running Totals"
Code Sample 2: Function to add a running total column, using an argument-defined table, SumColumn and GroupColumn:
(MyTable as table, SumColumn as text, GroupColumn as text) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Table.Column(Source,SumColumn),{0},(cumulative,SumColumn) => cumulative & {List.Last(cumulative) + SumColumn})),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType),
RemoveGroupColumn = Table.RemoveColumns(AddedRunningSum,{GroupColumn}),
FunctionResult = RemoveGroupColumn
in
FunctionResult
Hi,
I'm new in using PowerBI to make a report and one of the report that I'm making now requires a table that shows running sum by group. I tried following the query script I found here but encountered a 'token eof expected' error that pointed to the "=>" symbol in this part: (MyTable as table, MyColumn as number) =>
Here's the script:
let
Source = Table.Combine({#"DMU Sched Opening", #"DMU Sched Closing"}),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
#"Grouped Rows" = Table.Group(Source, {"Hours"}, {{"Data", each fn_AddCumul(_,"Data.No of Tablets per DMU.# Tablets"), TableType}}),
//type table [Location=text, Opening Day=text, Hours=time, #"No of Tablets per DMU.# Tablets"=number]}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Location", "Opening Day", "Hours", "No of Tablets per DMU.# Tablets","Running Sum"}, {"Data.Location", "Data.Opening Day", "Data.Hours", "Data.No of Tablets per DMU.# Tablets","Running Sum"})
in
#"Expanded Data"(MyTable as table, MyColumn as number) =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Cumul", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Table.Column(Source,MyColumn),{0},(Cumulative,MyColumn) => Cumulative & {List.Last(Cumulative) + MyColumn})),
Cumul = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
CumulMay I know what's wrong with it? Thank you in advance for the help!
The line where you call the function includes:
each fn_AddCumul(_,"Data.No of Tablets per DMU.# Tablets"),where the second argument is supposed to be a column name.
Is "Data.No of Tablets per DMU.# Tablets" definitely your column name? At a glance it looks like a combination of a merged column from another table, combined with the name of a previous M step?Thank you for your reply
I have now changed to the below but I'm still getting the same error 😞
let
Source = Table.Combine({#"DMU Opening", #"DMU Closing"}),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
#"Grouped Rows" = Table.Group(Source, {"Hours"}, {{"Data", each Cumul(_,"NumTablets"), type table [Location=text, Opening Day=text, Hours=time, NumTablets=number]}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Location", "Opening Day", "NumTablets","Running Sum"}, {"Location", "Opening Day", "NumTablets","Running Sum"})
in
#"Expanded Data"(MyTable as table, MyColumn as number) =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Cumul", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Table.Column(Source,MyColumn),{0},(Cumulative,MyColumn) => Cumulative & {List.Last(Cumulative) + MyColumn})),
Cumul = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
Cumul
