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