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

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

thanks you very much

Why is it ?

{[Index] - 2}




  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

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

    Positioning in PQ starts from zero. That's why ğŸ˜‰