Forum Discussion
Substract rows from the same column
Hello, I´m tryin to substract rows from the same column (FPR), I need to substract the next value from the previous value acorrding to the date like this: FPR from 30/04/2019 - FPR from 31/03/2019, then 30/05/2019 - 30/04/2019 etc ...
Hi OscarSuarez10,
sort the table by the column TIME, create an index column starting with 0 and use the following formula for addressing the previous row:
SortByTime = Table.Sort(#"Previous Step", {"TIME"}), IndexColumn = Table.AddIndexColumn(SortByTime, "Index", 0, 1), Substract = Table.AddColumn(IndexColumn, "Substract", each [FPR] - IndexColumn[FPR]{[Index] - 1})The value of current row: [FPR] or IndexColumn[FPR]{[Index]}
The value of previous row: IndexColumn[FPR]{[Index] - 1} - give me the value of column FPR on the index [Index] - 1.
3 Replies
Hi OscarSuarez10,
sort the table by the column TIME, create an index column starting with 0 and use the following formula for addressing the previous row:
SortByTime = Table.Sort(#"Previous Step", {"TIME"}), IndexColumn = Table.AddIndexColumn(SortByTime, "Index", 0, 1), Substract = Table.AddColumn(IndexColumn, "Substract", each [FPR] - IndexColumn[FPR]{[Index] - 1})The value of current row: [FPR] or IndexColumn[FPR]{[Index]}
The value of previous row: IndexColumn[FPR]{[Index] - 1} - give me the value of column FPR on the index [Index] - 1.
Thank You for answering, that helped me a lot
But now I only have to substract the first value of 2019 and the last value of 2019, then the first value of 2020 and then the last value of 2020 etc ...
Hi OscarSuarez10,
I see.
Please close this topic and open a new one, because it requires another approach. You can also mention me and I'll try to respond as soon as possible.
