Problem with [Column Name] - [Column Name]{[Index]-1}

[MXSven]

Hi everybody,
I am facing the problem, that adding " {[Index]-1} " to the formula leads to an error - 

Expression.Error: We cannot convert the value 12345 to type List.

Any suggestions how to fix this ?

[MXSven]

I tried if [Index]>0 while starting the index with 1

it runs through, but everything is "0" again,
in addition 99% seems to be an error.

just for clarification

[Column Name] - #"my_table"[Column Name]{[Index]-1}

or is it 

#"my_table" [Column Name] - #"my_table"[Column Name]{[Index]-1} ?

[AlienSx · Accepted solution]

my bad, sorry

If your index column starts with 1 then use the following code: 

if [Index] > 1 then [col2] - my_table[col2]{[Index] - 2} else null

If your Index column starts with 0 then 

if [Index] > 0 then [col2] - my_table[col2]{[Index] - 1} else null

[MXSven]

thanks you very much

Why is it ?

{[Index] - 2}

[AlienSx]

Another example. Consider list of 2 items: {1000, 2000}:

List.Count({1000, 2000}) = 2

List.Positions({1000, 2000} = {0, 1}

1st item of the list: {1000, 2000}{0} = 1000

2nd item of the list: {1000, 2000}{1} = 2000

Hope you've got the idea. 

[AlienSx]

Positioning in PQ starts from zero. That's why 😉 

[AlienSx]

Hello, @MXSven You are trying to acces field value from previous row. But in your current context (you are staying at the current row) the value of [Invest History.Portfolio] is just a value of that field in the current row. So it's not the whole column of the table (which is list). Add table name like my_table[Invest History.Portfolio]{[Index] - 1]} and let us know if it workes. 

[MXSven]

I tried as suggested: 

[Column Name] - #"my_table"[Column Name]{[Index]-1}

it runs through, but everything is "0",
in addition 99% seems to be an error.

Any ideas ?

[AlienSx]

Make your Index column start from 0 like in the code below. Or change your formula to "if [Index] > 1 ..." Positioning in PQ lists goes from zero. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWJVkoCsozBrGQgywLMSgGyLMGsVJA6oMJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col2", Int64.Type}}),
    my_table = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    test = Table.AddColumn(my_table, "test_column", each if [Index] > 0 then [col2] - my_table[col2]{[Index] - 1} else null)
in
    test

[AlienSx]

pardon, correction: 

Or change your formula to "if [Index] > 0 ..."