Power Query Problems

Create a Power Query Custom Column Using the Previous Row

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 869 views

Question details

Create a Power Query custom column that uses a value from a previous or offset row.

Create a Power Query Custom Column Using the Previous 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.
Before you start

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.

Solution 1Recommended

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.

1
Open the query

In Excel, select the source table and choose Data > From Table/Range, or open an existing query from Data > Queries & Connections.

2
Set the row order

In Power Query Editor, use the relevant column's sort menu to arrange the rows in the order needed for the calculation.

3
Add a zero-based index

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.

4
Create the custom column

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.

5
Load the result

Check the first row and a few subsequent rows, then choose Home > Close & Load to return the transformed data to Excel.

Use an index to reference the previous row in Power Query
How the fallback works: The first row has index 0, so index -1 does not exist. The try and otherwise expression returns the current Amount in that case; it also catches other errors in that expression.
Free Microsoft Office alternative

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.

Compare values across spreadsheet rows using formulas in WPS SpreadsheetsOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interface for an easier transitionWork with a free, lightweight office suite
microsoft office alternative - wps office

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.