Create a Power Query Custom Column Using the Previous Row
Question details
Create a Power Query custom column that uses a value from a previous or offset row.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user wants to perform a row-by-row calculation similar to an Excel OFFSET formula during a Power Query transformation.
- Observed behavior
- The calculated column should use a value from an offset row and handle positions where that row does not exist.
Confirm which column supplies the current value and which supplies the previous-row value. Sort the data into the required order before adding an index, and ensure the calculation columns have numeric data types.
Use an index to reference the previous row in Power Query
Add a zero-based index and use it to retrieve a value from the preceding row, with a fallback for the first row.
This example subtracts the previous row's Order value from the current Amount when Amount is less than 50. Otherwise, it multiplies Amount by 10. For the first row, the fallback returns the current Amount.
In Excel, select the source table and choose Data > From Table/Range, or open an existing query from Data > Queries & Connections.
In Power Query Editor, use the relevant column's sort menu to arrange the rows in the order needed for the calculation.
Choose Add Column > Index Column > From 0. In Applied Steps, confirm that the new step is named Added Index; if it has a different name, use that name in the formula.
Choose Add Column > Custom Column, enter a column name, and paste: if [Amount] < 50 then try [Amount] - #"Added Index"[Order]{[Index]-1} otherwise [Amount] else [Amount] * 10. Replace Amount and Order with your column names and adjust the conditions as needed, then select OK.
Check the first row and a few subsequent rows, then choose Home > Close & Load to return the transformed data to Excel.

Work with spreadsheet data in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for opening and editing spreadsheets. Its Microsoft Office format compatibility makes moving existing files straightforward, although it does not fix Excel Power Query issues.

Frequently Asked Questions
How do I reference the previous row in Power Query?
Add an index with Add Column > Index Column > From 0, then use a custom column expression such as #"Added Index"[Order]{[Index]-1}. Replace the step and column names with those in your query.
Why does the first row need a fallback?
A zero-based index gives the first row position 0, so there is no row at position -1. Wrap the lookup or calculation in try and otherwise to supply a fallback value.
Can I reference a row other than the previous one?
Yes. In the Custom Column formula, change [Index]-1 to another offset, such as [Index]-2 or [Index]+1. Use try and otherwise to handle positions outside the table.
Does the sort order affect which row is considered previous?
Yes. The previous row is determined by the order in the indexed query step. Sort the data before choosing Add Column > Index Column so the index reflects the order you need.




