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 want to thank you for responding to my request, but the code has not worked for me, I don't know what I'm doing wrong I share the files to see what I'm doing wrong
let
Origen = Excel.Workbook(File.Contents("C:\Users\J024919\Downloads\Prub.xlsx"), null, true),
Est_Table = Origen{[Item="Est",Kind="Table"]}[Data],
#"Tipo cambiado" = Table.TransformColumnTypes(Est_Table,{{"zona", type text}, {"Estado", type text}, {"Estanque", type text}, {"Ciclo", type text}, {"Area(ha)", type number}, {"Organismos sembrados", type number}, {"Densidad (cam/ha)", Int64.Type}, {"Peso siembra", type number}, {"Fecha siembra", type date}, {"Fecha muestreo", type date}, {"Dia", Int64.Type}, {"Semana", Int64.Type}, {"Peso", type number}, {"Organismos actuales", type number}, {"Densidad (Cam/m2)", type number}, {"Densidad Actual", type number}, {"Org Raleo/mt2", type number}, {"Sobrevivencia", type number}, {"Biomasa Kg", type number}, {"Raleo", Int64.Type}, {"Biomasa Kg/ha", type number}, {"Alimento semanal (Kg)", Int64.Type}, {"Alim acum Kg", Int64.Type}, {"F.C.A. Ciclo", type number}}),
TableType = Value.Type(Table.AddColumn(#"Tipo cambiado", "Running Sum", each null, type number)),
#"Grouped Rows" = Table.Group(Est_Table, {"Estanque", "Ciclo"}, {{"AllData", each fnAddRunningSum (_, "Raleo"), TableType}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Raleo", "Running Sum"}, {"Raleo", "Running Sum"})
in #"Expanded AllData"
Running Funcion
(MyTable as table, value as text) as table =>
let
Source = Table.Buffer(Est),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Table.Column(Source, value),{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + Number.From(cost)})),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
AddedRunningSum
(Arguments) (Est) (CICL) are = "Estanque" "Ciclo"
(ve) is = "Raleo"
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 existIf you have an email where I can send you the files, I thank youHi Jcarofi,
if this is still not resolved, you can save the sample files to Google Drive, Dropbox or alike and send me a link via private message. I can have a look later today.
Thanks,
JB