Initially, I will present two methods that are regarded as the quickest for performing running total calculations.
Method-1:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.FromColumns(
{Source[Value]} &
{
List.Generate(
() => [RT = Source[Value]{0}, n = 0 ],
each [n] < List.Count(Source[Value]),
each [ n = [n] + 1, RT = [RT] + Source[Value]{[n]}],
each [RT])
},
{"Data", "RT"}
)
in
Result
Method-2:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.FromColumns(
{Source[Value]} &
{
List.Skip(List.Accumulate(Source[Value],{0}, (s,c) => s & { List.Last(s) + c}))
},
{"Value", "RT"}
)
in
Result
In addition to these two methods,
adding an index, creating Running Total column by utilizing the List.FirstN function is commonly used technique. While not seen as the most effective approach, it remains a useful option to have at our disposal.
I will modify this method slightly by implementing Table.ReplaceValue function.
Rather than adding two separate columns, we can introduce an index column and convert this index into a running total value.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.ReplaceValue(
Table.AddIndexColumn(Source,"RT",1),
each [RT],
each null,
(x,y,z) => List.Sum(List.FirstN(Source[Value],y)),
{"RT"}
)
in
Result
x => Current value
y => Old Value
z => New Value
Comments