Add index columns
Number rows meaningfully by sorting first and choosing the start value.
~7 min read · beginner · Module 9: Duplicates and keys
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Sort rows on more than one column
An index numbers rows in their current order. It's useful for line numbers, for keeping an original order before reshaping, as a tie-breaker in sorts, and as a simple surrogate key.
let
Source = #table({"Customer", "Spend"}, {{"Cleo", 80}, {"Ana", 120}, {"Ben", 120}, {"Dev", 35}}),
Sorted = Table.Sort(Source, {{"Spend", Order.Descending}, {"Customer", Order.Ascending}}),
Ranked = Table.AddIndexColumn(Sorted, "Rank", 1, 1, Int64.Type)
in
RankedResult
| Customer | Spend | Rank |
|---|---|---|
| Ana | 120 | 1 |
| Ben | 120 | 2 |
| Cleo | 80 | 3 |
| Dev | 35 | 4 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Arguments: new column name, starting value, increment, type. The editor offers From 0 and From 1; reports usually want 1. A custom increment (for example 10) leaves room to insert lines by hand in a planning sheet.
Sort first
An index added before sorting records the import order, which is rarely meaningful. Sort by the business order (date, then ID to break ties), then add the index. In the example Ana and Ben tie on spend; the second sort key decides that Ana is 1, every time.
Index as a row number, not a rank
Table.AddIndexColumn always counts 1, 2, 3, 4. A competition-style rank, where tied customers both get rank 1 and the next gets 3, needs a different approach: count how many rows have a strictly higher value and add 1. That's a group-based calculation covered in the All Rows lesson.
Index per group
For line numbers that restart for each order or each customer, add the index inside each group (Group By with All Rows, then Table.AddIndexColumn on each nested table). The same lesson shows the pattern.
Where to find this in Excel
- Excel for Windows
- Add Column > Index Column
- What you see
- The default index starts at 0 and goes up by 1; Custom lets you set the start and the increment.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Add an index column (Microsoft Learn).
Common mistakes
- Adding the index before sorting, so numbers follow import order.
- Treating an index as a rank when there are ties.
- Using an index as a permanent ID even though it renumbers on every refresh when rows change.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: Add Column → Index Column → From 0 / From 1 / Custom….
- An index is recalculated on every refresh in both hosts; it's not a stable identifier across refreshes.
- The simulator supports the start, increment and type arguments of Table.AddIndexColumn.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Number claims in date order (consultancy)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- beginner~5 minPro
Number claims in date order (consultancy)
Sort claims by date and add a Line number starting at 1.
- beginner~5 minPro
Number claims in date order (school)
Sort claims by date and add a Line number starting at 1.
- advanced~14 minPro
Interview task: month-over-month revenue change (kite shop)
Total revenue per month, then show the change from the previous month (null for the first month), in month order.
- advanced~14 minPro
Interview task: month-over-month revenue change (bakery)
Total revenue per month, then show the change from the previous month (null for the first month), in month order.
- advanced~14 minPro
Interview task: month-over-month revenue change (bike rental)
Total revenue per month, then show the change from the previous month (null for the first month), in month order.