Calculate a value based on its previous value in the same column

[Goodkat]

Dear Power Query forum,

I am stuck with a logically simple and in an excel spreadsheet fairly easy to realize problem; but in PQ it exceeds my current capabilities. Also search in the internet, including this forum and youtube, did not provide a tangible result.
I have a list of year & month combinations which I want to enrich with a decreasing value, based on the previous row value multiplied by a rate of decrease. In the spreadsheet the column [DeclineExcel] shows my aspired outcome with excel formulas. But in the particular case I want to use this, I cannot do it in excel. And also as thinking about this in PQ intrigued me, I want to learn how to achieve that in PQ.
So I added an index column and tried to reference with the previous step and the current index -1. But it failed and I also sense that this approach is too simple to work.

https://c.gmx.net/@324888734501700174/fqDf8Y0Tnd_LIMscLcrNkQ

Does the forum have any idea or an example which PQ statements can solve such task?

Thank you!

Best regards, Andreas

[MarkLaf · Accepted solution]

This just goes through ValueStart and Rate and applies the same formula as in your Excel.

Sample

Year	Month	ValueStart	Rate
2025	4	14	0.015
2025	5	null	0.015
2025	6	null	0.015
2025	7	null	0.015
2025	8	null	0.015
2025	9	null	0.015
2025	10	null	0.015
2025	11	null	0.015
2025	12	null	0.015
2026	1	null	0.015
2026	2	null	0.015
2026	3	null	0.015
2026	4	null	0.015
2026	5	null	0.015
2026	6	null	0.015
2026	7	null	0.015
2026	8	null	0.015
2025	2	28	0.023
2025	3	null	0.023
2025	4	null	0.023
2025	5	null	0.023
2025	6	null	0.023
2025	7	null	0.023
2025	8	null	0.023
2025	9	null	0.023
2025	10	null	0.023
2025	11	null	0.023
2025	12	null	0.023
2026	1	null	0.023
2026	2	null	0.023
2026	3	null	0.023
2026	4	null	0.023

M (advanced editor)

let
    Source = Sample,
    ValueStarts = List.Buffer( Source[ValueStart] ),
    Rates = List.Buffer( Source[Rate] ),
    GenDecline = List.Generate(
        ()=>[i=0,v=ValueStarts{i}], each [i] < Table.RowCount(Source), 
        each [
            i = [i] + 1, 
            v = if ValueStarts{i} is null then [v]*(1-Rates{i}) else ValueStarts{i}
        ],
        each [v]
    ),
    CombineColumns = Table.FromColumns( 
        Table.ToColumns(Source) & {GenDecline}, 
        // dynamically adds new column name and type to Source
        // with given sample, equivalent of: 
        // type table [Year=Int64.Type,Month=Int64.Type,ValueStart=number,Rate=number,Decline=number]
        type table Type.ForRecord( 
            Type.RecordFields( Type.TableRow( Value.Type( Source ) ) ) 
            & [Decline=[Type=type number,Optional=false]], 
            false 
        ) 
    )
in
    CombineColumns

Output

[Goodkat]

Dear MarkLaf,

thank you so much for providing your solution! List.Generate I did not have on my radar. Your code works as expected and directed me towards several aspects I must now further explore with the knowledge materials out there.

Best regards, Andreas

[jgeddes]

There are probably more efficient ways to do this. But this should work for you. It relies on grouping the table into sections and generating an output list for each section. A nested zero based index is then used to extract the desired output from the index position of the list.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdFBDoAgDETRu7AmhlaoeBbj/a+hjYmK6XcBizeQNNNtS1q0pZzqecSvMhVpac934idyA1/AO/gKLoUCoUCDwMeMPrjT+xm8gkcVGVRkUJH9VORzar8Snd/JM+noFbyBG/gC3sFX8NcyP4FQoEEwLnN0eh9VNC7TfT8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t, ValueStart = _t, Rate = _t]),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {
            {"Year", Int64.Type}, {"Month", Int64.Type}, {"ValueStart", Int64.Type}, {"Rate", type number}
        }
    ),
    //this uses the ValueStart column to group the the table into sections that can be indexed and can have the list generate fuction applied.
    //it would be more stable if a more reliable grouping column was used.
    #"Filled Down" = 
    Table.FillDown(
        #"Changed Type",
        {"ValueStart"}
    ),
    //group the table by ValueStart and Rate, getting a nested table row count and all rows in the table
    #"Grouped Rows" = 
    Table.Group(
        #"Filled Down", 
        {"ValueStart", "Rate"}, 
        {
            {"Count", each Table.RowCount(_), Int64.Type}, 
            {"AllRows", each _, type table [Year=nullable number, Month=nullable number, ValueStart=nullable number, Rate=nullable number]}
        }
    ),
    //use the List.Generate function to create a list of outputs 
    Custom2 = 
    Table.AddColumn(
        #"Grouped Rows", 
        "list", 
        each 
        List.Generate(
            ()=> [
                    x=[ValueStart],             //initial x value is the ValueStart value for the row
                    y=[Rate],                   //y is always the Rate for the row
                    z=[Count]                   //z is the number of rows in the nested table, i.e., the number of output rows we need
                ], 
                each [z] > 0,                   //iterates as long as z is > 0 
                each [
                        x= [x] - ([x]*[y]),     //for every iteration, x is set to the previous value of x minus the previous value of x times the rate
                        y=[y],                  //y never changes
                        z = [z]-1               //z is decreased by one for every iteration
                    ], 
                each [x]                        //return the value of x to the list
        ), 
        type list
    ),
    //add an index column to the nested AllRows table. IMPORTANT that this starts at 0 for list indexing reasons.
    Custom1 = 
    Table.TransformColumns(
        Custom2, 
        {
            {"AllRows", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type), type table [Year=nullable number, Month=nullable number, Index=nullable number]}
        }
    ),
    //expand the needed columns from the nested table to new rows
    #"Expanded AllRows" = 
    Table.ExpandTableColumn(
        Custom1, 
        "AllRows", 
        {"Year", "Month", "Index"}, 
        {"Year", "Month", "Index"}
    ),
    //add a column that displays the value from the list at the current row's index position. 
    Custom3 = 
    Table.AddColumn(
        #"Expanded AllRows", 
        "Output", 
        each [list]{[Index]}, 
        type number
    ),
    //sort the needed columns
    Custom4 = 
    Table.SelectColumns(
        Custom3, 
        {"Year", "Month", "ValueStart", "Rate", "Output"}
    )
in
    Custom4

[Goodkat]

Dear jgeddes,

thank you for taking the time and come up with a solution. I have implmented the code in my sample file and it works! For efficiency I think I will go for now with the other solution in this thread, but as in your code some other commands are featured I will also keep it to learn more about these commands.

Best regards, Andreas