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
It doesn't work, I already tried
with a similar data I need to calculate the column accumulated by 2 arguments.
Ok.
This is a modified fnAddRunningSum - nothing really important, just made the values column name a variable to untie the internal data structure to the main table from the computation logic in the function:
(MyTable as table, values 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, values),{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + Number.From(cost)})),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
AddedRunningSum
This is the main table and the call to the function above using your sample data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKlTSUUoEYkMDpVgdBBeVZ4xPEsJLxsozwSdpik0yBdVKGNcMlYuNB3JPEgbPBJVrhE1tMqraZKyS5hiSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Est = _t, CICL = _t, ve = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Est", type text}, {"CICL", type text}, {"ve", type number}}),
TableType = Value.Type(Table.AddColumn(#"Changed Type", "Running Sum", each null, type number)),
#"Grouped Rows" = Table.Group(Source, {"Est", "CICL"}, {{"AllData", each fnAddRunningSum (_, "ve"), TableType}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"ve", "Running Sum"}, {"ve", "Running Sum"})
in #"Expanded AllData"Please note that the function call has changed slightly as we added the second parameter (value column name) to the function which calculates running totals.
This is the output of the main table/query:
EstCICLveRunning Sum
| q | a | 10 | 10 |
| q | a | 0 | 10 |
| q | a | 30 | 40 |
| q | a | 0 | 40 |
| q | a | 0 | 40 |
| c | a | 0 | 0 |
| c | a | 40 | 40 |
| c | a | 0 | 40 |
| c | a | 50 | 90 |
| c | a | 0 | 90 |
| d | a | 30 | 30 |
| d | a | 60 | 90 |
| d | a | 0 | 90 |
| d | a | 0 | 90 |
| q | b | 0 | 0 |
| q | b | 40 | 40 |
| q | b | 20 | 60 |
| q | b | 0 | 60 |
| c | b | 40 | 40 |
| c | b | 0 | 40 |
| c | b | 70 | 110 |
| c | b | 0 | 110 |
Please let me know if you have any questions.
Kind regards,
JB
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 exist