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
Hey MarcelBeug ,
I want to just do the cumulative sum without any grouping action using m query.Can you guide me through the solution for this Problem.
Hi subhashree_r ,
The actual part that does grouping in the original post was where List.Accumulate appears.
This is the function that does the actual TotalSum job and can be used without the GroupBy depends on a scenario you want to implement. There is a way to avoid grouping using custom functions in complex scenarios (where you do not need a SumTotal across the entire table), but it makes the code quite heavy to run and complex to read and understand. Why would you need it?
Kind regards,
JB