cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
optimusprime Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Super User
Super User

Re: How to do a running Sum by group in Power Query?

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)
7 REPLIES 7
optimusprime Frequent Visitor
Frequent Visitor

Re: How to do a running Sum by group in Power Query?

anyone?

Super User
Super User

Re: How to do a running Sum by group in Power Query?

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)
78chris Frequent Visitor
Frequent Visitor

Re: How to do a running Sum by group in Power Query?

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

78chris Frequent Visitor
Frequent Visitor

Re: How to do a running Sum by group in Power Query?

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
cyongt_bdf Frequent Visitor
Frequent Visitor

Re: How to do a running Sum by group in Power Query?

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

 

Cheers!

wgjunsay Frequent Visitor
Frequent Visitor

Re: How to do a running Sum by group in Power Query?

Hello @MarcelBeug , I tried using the function, what do I change if I want to group it by a different column ? 

 

Thanks !

cyongt_bdf Frequent Visitor
Frequent Visitor

Re: How to do a running Sum by group in Power Query?

Hi @wgjunsay ,

 

You may apply different "group by" column in the code (instead of "BU"):

#"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", fnAddRunningSum, TableType}})

Cheers!