Hi,
I have a challenge where I have landed on that I have to conduct multiple running totals (RT) over a data set of some 1-2000 records (growing).
The issues I have are:
1. The number of recursions are manually decided based on the data, not nice
2. It is very slow, even worse...
Key questions:
1. Is there a way to recursively call the RT function based on a global counter variable?
2. Can multiple function calls reuse a table in memory (each RT call creates a buffer) , I am assuming that this would speed up things.
3. Are there other ways to speed this up, any ideas of applicable reference sites would be appreciated.
The RT function I found on internet and looks like this, works like a sharm:
= ( New_Column as text, MyTable as table, Sum_Column as text) =>
let
Source = MyTable,
AmountBuffer= List.Buffer( Table.Column( MyTable, Sum_Column)),
SumAV = List.Generate(
() => [ RunningTotal = AmountBuffer{0}, Counter = 0 ],
each [Counter] < List.Count(AmountBuffer),
each [ RunningTotal = List.Sum({[ RunningTotal ], AmountBuffer{[Counter]+1}}), Counter = [Counter] + 1 ],
each [RunningTotal]
),
Consolidation = Table.FromColumns(
Table.ToColumns(Source) & {Value.ReplaceType( SumAV, type {Currency.Type})},
Table.ColumnNames(Source) & {New_Column}
)
in
Consolidation
