cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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.

 

2017-10-25 09_02_31-Book1 - Excel.png

 

 

1 ACCEPTED SOLUTION

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
Specializing in Power Query Formula Language (M)

View solution in original post

45 REPLIES 45
Jcarofi
Frequent Visitor

Please and if I want the accumulated taking into account the BU and Location columns
Anonymous
Not applicable

These 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.

 

Jcarofi_0-1631137802304.png

 

Anonymous
Not applicable

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

qa1010
qa010
qa3040
qa040
qa040
ca00
ca4040
ca040
ca5090
ca090
da3030
da6090
da090
da090
qb00
qb4040
qb2060
qb060
cb4040
cb040
cb70110
cb0110

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"

Jcarofi_0-1631144567519.png

 





Anonymous
Not applicable

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

If you have an email where I can send you the files, I thank you
Anonymous
Not applicable

Hi @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

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

I need a help with de Mcode

Netrelemo
Helper III
Helper III

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. 

Anonymous
Not applicable

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!

78chris
New Member

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
    AddedRunningSum

This is great, though how should I call this function now that it has two arguments?

@78chris 

 

@78chris 

Thanks Chris, you posted this code to allow me to select the specific column :

 

(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
    AddedRunningSum

 

Below is the original code, which calls this function. What changes need to be made to this to call the function, now that it has two arguments?

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"

Hi @TheOctopusIAm ,

 

You may try this code where "ColumnHeader" is your column header name:

let
    Source = Table1,
    TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
    #"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", fnAddRunningSum(_, "ColumnHeader"), TableType}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Location", "Month", "Cost", "Running Sum"}, {"Location", "Month", "Cost", "Running Sum"})
in
    #"Expanded AllData"

Thanks for the reply @cyongt_bdf. That seems logical, though I'm now getting the following error:

 

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

 

 

I've had a search around, but can't find any similar scenarios that generate this same error.

Any thoughts? 

Hi @TheOctopusIAm ,

 

I missed the each keyword, please try this code instead:

let
    Source = Table1,
    TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
    #"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", each fnAddRunningSum(_, "ColumnHeader"), TableType}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Location", "Month", "Cost", "Running Sum"}, {"Location", "Month", "Cost", "Running Sum"})
in
    #"Expanded AllData"

Hi @cyongt_bdf!

 

I am having trouble getting this to work. I have to apologize, I'm new to M and power queries custom functions.

 

When I copied the original table submitted by OP, made it into a source and tried to apply the formulas on it chaning "ColumnHeader" to "Costs". However, I received the following error:

 

Expression.Error: The import AddedRunningSum matches no exports. Did you miss a module reference?

 

How do I solve this? Additionally, is the name of the function "query" important, i.e. does it need to "fnAddRunningSum" or "AddedRunningSum"?

 

My actual data is regarding COVID-19 from here https://data.europa.eu/euodp/en/data/dataset/covid-19-coronavirus-data/resource/55e8f966-d5c8-438e-8..., where I'm trying to apply this running sum per country (e.g. "cases" grouped by "countriesAndTerritories").

 

Thank you very much!


Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.