Forum Discussion

Goodkat's avatar
Goodkat
Helper II
6 months ago
Solved

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

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, in...
Need Help
  • MarkLaf's avatar
    MarkLaf
    6 months ago

    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

     

     

MarkLaf's avatar
MarkLaf
Super User
6 months ago

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