Forum Discussion
How to do a running Sum by group in Power Query?
Hi Everyone,
I am trying to do a running sum by group in Power Query (m language). Thank you.
All solutions I found was to use DAX which I cannot use for my data at this time.
Here is what my data looks like, I would like a running sum of the cost in a new column.
Thank you all.
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
46 Replies
Unbelievable.
Power Bi has invented a billion new functions and formulas but needs 20 lines of code across a query and embedded function to calculate the running total?
Unbelievable.
Hello
To MarcelBeug
This is very good.
Is it possible to give the Field to use (Cost here) as a parameter of the function ?
Thanks a lot
78Chris
Hello
I found a solution and give it
(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 AddedRunningSumThis is great, though how should I call this function now that it has two arguments?
78chris
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
AddedRunningSumHi MarcelBeug,
Your solution is great!! It helps me a lot to minimize the performance issue based on DAX in the visuals!
I think it is also possible to modify the fnAddRunningSum function slightly to avoid List.Skip:
(MyTable as table) as table => let Source = Table.Buffer(MyTable), TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)), Cumulative = List.Accumulate(Source[Cost],{},(cumulative,cost) => cumulative & {List.Last(cumulative, 0) + cost}), AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType) in AddedRunningSumCheers!
Hi MarcelBeug
Would be great if you could help me understand how the below statement is working in the code that you provided. I am having a hard time understanding it.
Cumulative = List.Skip(List.Accumulate(Source[SHIPMENT],{0},(cumulative,SHIPMENT) => cumulative & {List.Last(cumulative) + SHIPMENT}))
Thanks in advance!
Please and if I want the accumulated taking into account the BU and Location columnsThese lines:
#"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"})would look like:
#"Grouped Rows" = Table.Group(Source, {"BU", "Location"}, {{"AllData", fnAddRunningSum, TableType}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Month", "Cost", "Running Sum"}, {"Month", "Cost", "Running Sum"})It doesn't work, I already tried with a similar data I need to calculate the column accumulated by 2 arguments.
