Today we are going to discuss about calculating running totals in Power Query in effective way using List. Accumulate and List. Generate. These 2 methods considered to be the fastest method, since they add last item of running total with current item. Other methods which use expandable list to calculate running total, becomes slow in larger table.
Method-1:
With List. Accumulate,
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
RTList = List.Skip(List.Accumulate(Source[Sales],{0},(s,c) => s & {List.Last(s) + c})),
Final = Table.FromColumns(Table.ToColumns ( Source ) & {RTList},
Table.ColumnNames(Source) & {"Running Total"}),
ChType = Table.TransformColumnTypes(Final,{{"Date", type date},
{"Sales", type number}, {"Running Total", type number}})
in
ChType
'Source' step helps us to pick the sales table into power query editor.
in 'RTList' step,
list -> Source[Sales]
which picks 'sales' column and delivers as list which we are going to accumulate.
seed -> {0}
List with one element of zero.
accumulator ->
In the first iteration,
s is {0}
List. Last function will pick 0 and current item is 207.8, if we add this, we will get 207.8.
in order to append this with 's', we need to convert the summed amount into list, so we wrap it in curly braces.
At the end of first iteration, 's' will become {0,207.8}
In the 2nd iteration,
List. Last will pick 207.8 and current item is 3000 and the sum of these two values is 3207.8.
Now, 's' will become {0,207.8,3207.8} and so on.
In Final step,
we add this accumulated list as a column in Source step and change the data type of columns before loading into Excel.
Method-2:
with List. Generate,
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sales = Source[Sales],
RTotal = List.Generate(() => [RT = Sales{0}, n = 0], each [n] < List.Count(Sales),
each [RT = [RT] + Sales{n}, n = [n] + 1], each [RT]),
Final = Table.FromColumns(Table.ToColumns(Source) & {RTotal},
Table.ColumnNames(Source) & {"Running Total"}),
ChType = Table.TransformColumnTypes(Final,{{"Date", type date},
{"Sales", type number}, {"Running Total", type number}})
in
ChType
In RTotal step,
Initial values are,
RT -> Sales{0} -> 270.8
n -> 0
Conditions are,
Do iteration till 'n' is less than the count of sales column.
Next values in each iterations are,
RT -> [RT] + Sales{n}
n -> [n] + 1
in first iteration,
'n' = 1,
RT = 207.8 + Sales{1} which is 3000 = 3207.8
in 2nd iteration,
'n' = 2,
RT = 3207.8+51.5 = 3259.2 and so on.
in select argument, we pick 'RT' in each iteration.
In Final step,
we add this generated list as a column in Source step and change the data type of columns before loading into Excel.