Power QueryExercises

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
    Ranked

Result

CustomerSpendRank
Ana1201
Ben1202
Cleo803
Dev354

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.
Functions in this lesson: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.