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

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.

CodePudding user response：

Query 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1QGSegameBmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Factor = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Factor", type number}}),
    BF = List.Buffer(#"Changed Type"[Factor]),
    Result = Table.FromColumns(
        {   
            #"Changed Type"[Factor], fx(BF)
        },
        {
            "Factor",
            "Result"
        }

    )



in
    Result

Relevant Function

(values as list) as list =>

let 

result = List.Generate(

    () => [result = 100, counter = 0],
    each [counter] < List.Count(values),
    each [result = [result] * values{[counter]   1}, counter = [counter] 1 ],
    each [result]

)

in result

CodePudding user response：

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,

= Starting_Number * Number.Power(1 Growth%,[Index])

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"

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"