How to do incremental summing in same column with Power Query?

Question

I have a column [Sales Growth] which as five rows (say for 5 months). Where current row = 1.05% of preceding row or previous row. And it continues down with each new row.

In Excel its very easy Suppose I am in B2 all I have to do is B1*1.05. But, in Power Query, it turned out to be very difficult to solve. I have seen all kinds of solutions with double indexing and Index -1 etc which is good for running total but not in my case.

Answer 1

It is hard to tell exactly what you are looking to do. But if you start with some base number and always increase each row by the same % then its just a math compounding formula.

Result = Base * (1+Rate)^PeriodNumber

or in powerquery terms,

= Base * Number.Power(1+Rate,[Column_Containing_Period])

sample code:

let
Starting=100,
Growth=.05,
Source =  Table.FromList({0..50}, Splitter.SplitByNothing(), {"Index"}, null, ExtraValues.Error ),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Starting*Number.Power(1+Growth,[Index]))
in #"Added Custom"

or

let
Starting=100,
Growth=.05,
Source =  List.Transform({0..50}, each Starting*Number.Power(1+Growth,_)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Result"}, null, ExtraValues.Error)
in #"Converted to Table"