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 ...
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
I do no think that there is something wrong with the code. It looks Ok.
The error that you get is the "stack overflow", which potentially means that the calculation result does not fit into the variable type.
Could you please try to filter your table (after #"Tipo cambiado" step) to take only 10 top lines to prove that the concept works?
Do you have fairly large numbers in the "Raleo" column?
Thanks,
JB
As the function is written in the result, you start creating rows by mixing the (ponds) with the (cycles) creating combinations that do not exist