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 how to fix this ?

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

     

9 Replies

  • 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

         

  • 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}