Forum Discussion

MXSven's avatar
MXSven
Helper I
3 years ago
Solved

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

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 ...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    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

     

AlienSx's avatar
AlienSx
Super User
3 years ago

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's avatar
MXSven
Helper I
3 years ago

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's avatar
    AlienSx
    Super User
    3 years ago

    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's avatar
      AlienSx
      Super User
      3 years ago

      pardon, correction: 

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