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
@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
Cumul
May 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