Power Query M: Running Total within Groups

Question

I am looking to do a running total of MaxDurationOrder within groups rather than the entire dataset. I have the following formula that works for all rows:

= Table.AddColumn(#"Added Index", "Running Total", each List.Sum(List.FirstN(#"Added Index"[MaxDurationOrder],[Index])))

But I do have a column in my dataset for Option 1, Option 2, Option 3 that I'd like to split the running totals by. How would I get the running total WITHIN each option?

horseyride horseyride · Accepted Answer · 2020-04-28T19:27:06

Add an index. Use below code to add custom column which adds up MaxDurationOrder if it has a lower index and same GroupColumn

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index","RunningTotal",(i)=>List.Sum(Table.SelectRows(#"Added Index", each [GroupColumn]=i[GroupColumn] and [Index]<=i[Index]) [MaxDurationOrder]), type number ),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in  #"Removed Columns"

Alternatively, in two parts, create function fnRunningSum

(MyTable as table) =>
let Source = Table.Buffer(MyTable),
MyColumn="MaxDurationOrder",    
MyColumn2=Table.Column(Source,MyColumn),
TableType = Value.Type(Table.AddColumn(Source, "Cumul", each null, type number)),
Cumulative = List.Skip(List.Accumulate(MyColumn2,{0},(cumulative,MyColumn) => cumulative & {List.Last(cumulative) + MyColumn})),
Cumu = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in Cumu

Group using function, then expand

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sum = Table.Group(Source, {"GroupColumn"}, {{"AllData",fnRunningSum}}),
#"Expanded AllData" = Table.ExpandTableColumn(Sum, "AllData", {"MaxDurationOrder", "Cumul"}, {"MaxDurationOrder", "Cumul"})
in "Expanded AllData"

Power Query M: Running Total within Groups

1 Answers