Add a Next-Row Conditional Column in Excel Power Query
Question details
Create a Power Query column that assigns labels based on values in neighboring rows.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user wants to inspect the next row when assigning labels such as Top-2-2 or Top-2.
- Observed behavior
- The desired output should return the appropriate label when the specified neighboring-row condition is met and 0 otherwise. The exact rules for blank and final rows need clarification.
Define which current-row and next-row values produce each label, including what should happen when the next value is blank or the current row is last. Identify the column that determines row order before adding an index.
Use an index to check the next row in Power Query
Use this approach when a custom column needs to read a value from the following row.
The formula below is an example: it returns Top-2 when the next row's Label value is Top-2, and the text 0 otherwise. It does not establish the missing rule for Top-2-2; adapt it after defining all conditions.
In Excel, open the query in Power Query Editor. Sort the column that determines the intended row order, such as a timestamp, using its column-header menu.
Select Add Column > Index Column > From 0. Confirm that the new column is named Index and that the new applied step is named Added Index.
Select Add Column > Custom Column. Enter a name for the result and use: let nextValue = try #"Added Index"[Label]{[Index] + 1} otherwise null in if nextValue = "Top-2" then "Top-2" else "0". Replace Label with the actual source column name and adjust the conditions and outputs to match your rules.
Inspect the new column for ordinary rows, blank next values, and the final row. Adjust the formula as needed, then select Home > Close & Load.

Work with spreadsheet data in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface and Microsoft Excel format support, making it easy to move everyday spreadsheet work. It does not fix or replace the Excel Power Query step described above.

Frequently Asked Questions
Why should I sort the query before adding an index?
The index identifies rows in their current order. Sort by the intended order in the column-header menu before selecting Add Column > Index Column, so the next index points to the intended neighboring row.
What happens on the last row?
There is no next row. In the example formula, try ... otherwise null handles the missing row, and the condition returns the text 0.
How can I return Top-2-2 instead?
First define which current-row and next-row values should produce Top-2-2. Then edit the formula under Add Column > Custom Column to add an if condition for that combination before the remaining conditions.
Can I return a numeric zero instead of text?
Yes. In the custom column formula, replace "0" with 0. Keep in mind that the resulting column will contain both text labels and numbers.




