Forum Discussion
Expression.Error: We cannot convert the value to type List
Hi Anonymous,
the problem is in addressing the row.
#"Specific Column" = Table.AddColumn(#"Changed Type4","Specific", each if [Column1]{[Index]-1} = [Column1]{[Index]-2} and [Column2]{[Index]-1} = [Column2]{[Index]-2} then [Amount]{[Index]-1} - [Amount]{[Index]-2} else [Amount]{[Index]-1})"Each" interates over all rows and gives you a current row in every step. And it means, that [Column1] is a text value in current row and you access (Index-1)th character of the string, what you actually don't want. Therefore the error, that a text isn't a list.
A suggestion is for example followings:
= Table.AddColumn(#"Changed Type 4", "Previous Row", each #"Changed Type 4"[Column1]{[Index] - 1})It addresses the column Column1 of (index-1)th row of the table #"Changed Type 4".
Btw. not sure how performant it is, but it works for sure
Hi Anonymous ,
To understand your scenario better, please the data sample which could reproduce your scenario and your desired output so that we could help further on it. Or, ideally, share the pbix (beware of confidential data).
Best Regards,
Cherry
Hi Anonymous,
the problem is in addressing the row.
#"Specific Column" = Table.AddColumn(#"Changed Type4","Specific", each if [Column1]{[Index]-1} = [Column1]{[Index]-2} and [Column2]{[Index]-1} = [Column2]{[Index]-2} then [Amount]{[Index]-1} - [Amount]{[Index]-2} else [Amount]{[Index]-1})"Each" interates over all rows and gives you a current row in every step. And it means, that [Column1] is a text value in current row and you access (Index-1)th character of the string, what you actually don't want. Therefore the error, that a text isn't a list.
A suggestion is for example followings:
= Table.AddColumn(#"Changed Type 4", "Previous Row", each #"Changed Type 4"[Column1]{[Index] - 1})It addresses the column Column1 of (index-1)th row of the table #"Changed Type 4".
Btw. not sure how performant it is, but it works for sure