Forum Discussion
How to extract the values from two consecutive rows
Hi,
I have measurements of an equipment that produces tablets. The operator logs the value that the machine shows every 15 minutes. This value shows how many tablets were produced from the last reset.
I would like to see how many tablets were produced between two consecutive measurements.
My data looks like this:
Date&Time Measurement
| Date&Time | Measurement |
| 01/06/2016 06:00 | 0 |
| 01/06/2016 06:30 | 12000 |
| 01/06/2016 07:00 | 22000 |
| 01/06/2016 07:30 | 30000 |
| 01/06/2016 08:00 | 44000 |
| 01/06/2016 08:30 | 58000 |
| 01/06/2016 09:00 | 72000 |
| 01/06/2016 09:30 | 80000 |
| 01/06/2016 10:00 | 102000 |
I would need to add a new column that shows every 30 minutes how many tablets were produced. My table should be like this:
| Date&Time | Measurement | Tablets produced between two measurements |
| 01/06/2016 06:00 | 0 | 0 |
| 01/06/2016 06:30 | 12000 | 12000 |
| 01/06/2016 07:00 | 22000 | 10000 |
| 01/06/2016 07:30 | 30000 | 8000 |
| 01/06/2016 08:00 | 44000 | 14000 |
| 01/06/2016 08:30 | 58000 | 14000 |
| 01/06/2016 09:00 | 72000 | 14000 |
| 01/06/2016 09:30 | 80000 | 8000 |
| 01/06/2016 10:00 | 102000 | 22000 |
Thank you for the help.
Nandor
Hi Nandor. One way to do it would be to add an Index column to your dataset. While you're in the query editor, you can go to Add Column > Add Index Column. Then you can go to the Data view and Add New Column where you subtract the row where the index is one less the current row. Something like:
Column = TableName[Tablets] - LOOKUPVALUE(TableName[Tablets], TableName[Index], TableName[Index]-1)
Just be sure your data is sorted properly before adding the Index column!
Thank you!
2 Replies
Hi Nandor. One way to do it would be to add an Index column to your dataset. While you're in the query editor, you can go to Add Column > Add Index Column. Then you can go to the Data view and Add New Column where you subtract the row where the index is one less the current row. Something like:
Column = TableName[Tablets] - LOOKUPVALUE(TableName[Tablets], TableName[Index], TableName[Index]-1)
Just be sure your data is sorted properly before adding the Index column!
Thank you!