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

     

MXSven's avatar
MXSven
Helper I
3 years ago

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'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.