Previous value from one calculated column

[LuciaS]

Hi!

I want to create column in power query. It is count of previous value from new column / Rate . In first cell we need to strart with 1 as previous value and then continue with privious values. 

Is there some way to create this column in power query .

Def= if [Index]=1 then 1/[Rate] else List.Range(Source[Def],[Index]-1,1)/[Rate]

Thank you !

[LuciaS]

@AlienSx Only first cell is Ok others are with  Error 

Translation :

Expression.Error: Def table column not found.
Details:
Def

[AlienSx]

Formula can't find [Def] column in Source table. You either don't have a table named Source or colum named Def in there. Do you still have Zdroj table? Use it instead of Source in the code. 

[LuciaS]

@AlienSx Translation of Source is Zdroj in my language . I thaought that is ok to put there only "Source".  I repleace Source with name of table and there is anothr error 

Expression.Error: A circular reference was encountered during evaluation.

[AlienSx]

@LuciaS what you are trying to do is recursive calculations. So you should be using something like this (via List.Accumulate)

= Table.AddColumn(Source, "DEF", (w) => List.Accumulate(List.FirstN(Source[Rate], w[Index]), 1, (s,c) => s/c))

 or write a recursive function and call it to create DEF column

    f_recursive = (x, rates, i, max_i) =>
        let 
            a = x / rates{i}
        in if i = max_i then a else @f_recursive(a, rates, i+1, max_i),
    DEF = Table.AddColumn(Source, "DEF", (w) => f_recursive(1, Source[Rate], 0, w[Index] - 1))

[LuciaS · Accepted solution]

@AlienSx  Thanks for your solutions maybe I am doing someting wrong but there is still Expression.Error: A circular reference was encountered during evaluation. But  this works for me :

= List.Transform(List.Skip(List.Accumulate(#"Zmenený typ1"[Rate], {1}, (s,c)=> s & {List.Last(s)/c})), Number.From)

but thanks i appreciate it

[AlienSx]

I completely overlooked that you are trying to create [Def] column with this code 😂 It's not going to work. Give me some time. Taking your numbers into consideration overflow comes sooner than later. 

[LuciaS]

@AlienSx Hello, there is still Error becaouse of Def column which I am creating . Is tehre some way to sole it ? 

[AlienSx]

what kind of error? Select a cell with error and get back with PQ error message

[AlienSx]

Hello, @LuciaS List.Range returns you a list, not a single value. Providing that your index column starts with 1, try this instead

Def = if [Index]= 1 then 1/[Rate] else Source[Def]{[Index] - 2}/[Rate]