Row context: whole-table values and the previous row
Combine the current row with values from other rows in a custom column.
~11 min read · advanced · Module 17: Functions, each and scope
By Tahir Nas · Examples run in our simulator · Updated
Before this: Write and use custom functions, Add index columns
Inside each, [Amount] reads the current row only. Questions like 'what share of the total is this row?' or 'how much did the reading change since the previous row?' need values from other rows. Two patterns cover most cases.
Whole-table values: compute once, outside each
Compute the total in its own step. The each function can see every earlier step name, so it can use the total directly:
let
Spend = #table({"CostCentre", "Amount"}, {{"Ops", 600}, {"Sales", 300}, {"IT", 100}}),
Total = List.Sum(Spend[Amount]),
Shares = Table.AddColumn(Spend, "Share", each [Amount] / Total, type number)
in
SharesResult
| CostCentre | Amount | Share |
|---|---|---|
| Ops | 600 | 0.6 |
| Sales | 300 | 0.3 |
| IT | 100 | 0.1 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Writing List.Sum(Spend[Amount]) inside the each would also work, but it reads as 'recalculate the total for every row'. A named step is clearer and is computed once.
The previous row: use an index
Rows have no built-in 'previous'. Sort the table into the order that defines 'previous', add an index, and look up the row at index − 1 with Table{position}:
let
Readings = #table({"Day", "Meter"}, {{3, 140}, {1, 100}, {2, 125}, {4, 150}}),
Sorted = Table.Sort(Readings, {{"Day", Order.Ascending}}),
Indexed = Table.AddIndexColumn(Sorted, "i", 0, 1, Int64.Type),
Usage = Table.AddColumn(Indexed, "Used", each if [i] = 0 then null else [Meter] - Indexed{[i] - 1}[Meter], type number)
in
Table.RemoveColumns(Usage, {"i"})Result
| Day | Meter | Used |
|---|---|---|
| 1 | 100 | null |
| 2 | 125 | 25 |
| 3 | 140 | 15 |
| 4 | 150 | 10 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Indexed{[i] - 1} is the previous row as a record, and [Meter] reads its field. The first row has no previous one, so it's null rather than an error.
Performance
Looking up another row inside each is fine for thousands of rows and slow for millions, because each lookup scans the table. For large tables, a self-merge on index = index − 1 (merge the table with a copy whose index is shifted by one) does the same job in one join.
Where to find this in Excel
There's no dedicated button. Write the expression in a custom column (Add Column > Custom Column) or edit the step's M in the Advanced Editor (View > Advanced Editor).
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Add a custom column (Microsoft Learn); Use Power Query to transform data (the Power Query editor) (Microsoft Learn).
Common mistakes
- Recomputing a whole-table total inside every row instead of in a named step.
- Indexing before sorting, so 'previous' means previous in the file rather than in time.
- Not handling the first row, which has no previous row and raises an error.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: both patterns are written in Add Column → Custom Column; the editor has no button for 'previous row'.
- In Power BI, running differences that should respond to report filters belong in DAX; doing it in Power Query fixes the order and grain at refresh.
- The simulator supports
Table{position}row access and indexes as shown.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Functions and each quiz