Power Query Loop Calculation

Question

Hi, I am new to Power Query and have an issue with a task which is relatively easy to complete in excel.

I need to get a loop calculation where the next output is dependant on the previous output in two places. Please see below for a brief snapshot.

The Starting point is 100 and no calculation is required. Column B is fixed throughout. Column C values vary between 0 - 15.

The operations is (Previous Answer * Column B) + Previous Answer + Column C

I came across the following but I am unable to manipulate the code to carry out the multiplication in the first step.

let _b = #"Added Index"{0} in
List.Accumulate(
List.Combine(
{{_b},
List.Range(#"Added Index"[A], 0, [Index] + 1)}),
0, (s, c) => s + c)

Any help would be greatly appreciated!

Answer 1

@PQDM . Another option:

let  
    Source = Table.FromRows(  
        {  
            {"Dec", null, null}, {"Jan", -0.0023, 0},  
            {"Feb", -0.0023, 0}, {"Mar", -0.0023, 7},  
            {"Apr", -0.0023, 0}  
        },  
        type table [A=text, B=number, C=number]  
    ),  
    Index = Table.AddIndexColumn(Source, "Index", 0, 1),  
    Output = Table.AddColumn(Index, "Output", each  
        if [Index] = 0 then 100  
        else  
            Number.Round(  
                List.Accumulate(List.Skip(List.Range(Index[C], 0, [Index]+1)), 100,  
                    (state,current)=> (state * -0.0023) + state + current  
                ),  
                2  
            ),  
        type number  
    ),  
    RemovedIndex = Table.RemoveColumns(Output,{"Index"})  
in  
    RemovedIndex  

Answer 2

Hi @PQDM

let  
// Table for demo  
    Source = Table.FromRows(  
        {  
            {"Dec", null, null}, {"Jan", -0.0023, 0},  
            {"Feb", -0.0023, 0}, {"Mar", -0.0023, 7},  
            {"Apr", -0.0023, 0}  
        },  
        type table [A=text, B=number, C=number]  
    ),  
//  
    OutList = List.Generate(  
        ()=> [i = 0, out = 100],  
        each [i] < Table.RowCount(Source),  
        each [i = [i] +1, out = Number.Round(([out]*-0.0023) + [out] + Source[C]{i},2)],  
        each [out]  
    ),  
    ToColumns = Table.ToColumns(Source) & {OutList},  
    ToTable = Table.FromColumns(ToColumns,  
        Table.ColumnNames(Source) & {"Output"}  
    ),  
    ChangedType = Table.TransformColumnTypes(ToTable,  
        {{"Output", type number}}  
    )  
in  
    ChangedType  

Answer 3

Hello!

Thank you for the solution above. I am trying to extend it to include calculations for group by ID. Will you be able to help please