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

[Anonymous]

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.

[cyongt_bdf]

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!

[kotelo]

Hi people, I tried using this method and it worked, but only when I'm using group by with only one field.

Is there a way to make it work for more than one field? I want to group by name, year and month...

[cyongt_bdf]

Hi @kotelo ,

Yes, you may group more columns and it still works:

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

[cyongt_bdf]

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!