Table.AddIndexColumn Function in Power Query M (5 Examples)

Table.AddIndexColumn adds a new column to a table that numbers each row in sequence. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want a row number, a running counter, or a quick ID column on your data, this is the function you reach for.

Table of Contents

Syntax of Table.AddIndexColumn Function

Table.AddIndexColumn(table as table, newColumnName as text, optional initialValue as nullable number, optional increment as nullable number, optional columnType as nullable type) as table

where

table (required, table). The table you want to add the index column to.
newColumnName (required, text). The name of the new column.
initialValue (optional, nullable number). The number the index starts from. Defaults to 0 when omitted.
increment (optional, nullable number). How much each row goes up by. Defaults to 1 when omitted.
columnType (optional, nullable type). An optional declared type for the new column, for example Int64.Type. Omit it to let Power Query assign the type.

Returns: the same table with one extra column of sequential numbers, appended as the last column.

In plain terms, you hand it a table and a column name, and it tacks on a counter that numbers your rows.

Example 1: Add a default index column

You have a list of players and want to number them. With no extra arguments, the index starts at 0 and goes up by 1.

Here is the starting data:

Player	Team
Alice	Red
Ben	Blue
Cara	Green
Dan	Gold

Add an index column named Index:

let
    Source = Excel.CurrentWorkbook(){[Name="Players"]}[Content],
    Indexed = Table.AddIndexColumn(Source,"Index")
in
    Indexed

The new column is added at the end, numbering the rows from 0.

The result keeps your data and adds the counter:

Player	Team	Index
Alice	Red	0
Ben	Blue	1
Cara	Green	2
Dan	Gold	3

Because no initialValue was given, the first row is 0, not 1.

Example 2: Start the index at 1

Most of the time you want the count to begin at 1. Pass 1 as both the initialValue and the increment.

Here is the starting data:

Task	Owner
Brief	Mia
Design	Leo
Build	Ravi
Review	Nina

Add a Step column that starts at 1:

let
    Source = Excel.CurrentWorkbook(){[Name="Tasks"]}[Content],
    Indexed = Table.AddIndexColumn(Source,"Step",1,1)
in
    Indexed

The third argument sets the starting number, the fourth sets the step.

The result numbers each task from 1:

Task	Owner	Step
Brief	Mia	1
Design	Leo	2
Build	Ravi	3
Review	Nina	4

This is the most common pattern you will use.

Example 3: Use a custom increment

You can make the index jump by any amount. Here the counter starts at 10 and goes up by 10.

Here is the starting data:

Stop	Line
North	Express
Central	Express
Harbor	Express
South	Express

Add a Marker column that counts in tens:

let
    Source = Excel.CurrentWorkbook(){[Name="Stops"]}[Content],
    Indexed = Table.AddIndexColumn(Source,"Marker",10,10)
in
    Indexed

The initialValue and increment are both 10, so each row goes up by 10.

The result spaces the markers out:

Stop	Line	Marker
North	Express	10
Central	Express	20
Harbor	Express	30
South	Express	40

Spaced numbers like this are handy when you want room to insert values later.

Example 4: Build a row ID and move it to the front

The index always lands as the last column. To use it as an ID at the start of the table, add it, then move it with Table.ReorderColumns.

Here is the starting data:

Customer	Amount
Acme	120
Globex	85
Initech	240
Umbrella	60

Add an OrderID starting at 1001, then reorder it to the front:

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    WithID = Table.AddIndexColumn(Source,"OrderID",1001,1),
    Reordered = Table.ReorderColumns(WithID,{"OrderID","Customer","Amount"})
in
    Reordered

The first step adds the ID at the end, and Table.ReorderColumns pulls it to the front.

The result leads with the ID:

OrderID	Customer	Amount
1001	Acme	120
1002	Globex	85
1003	Initech	240
1004	Umbrella	60

Starting at 1001 gives you tidy four-digit IDs instead of 1, 2, 3. You can also concatenate in Power Query to bolt a prefix onto the number for IDs like ORD-1001.

Example 5: Number rows within each group

This one comes up a lot in real work: numbering rows inside each category, so the counter restarts for every group. You group the table, add an index to each group’s sub-table, then combine.

Here is the starting data:

Region	Rep
East	Tom
East	Sara
West	Omar
East	Priya
West	Lily

Group by Region, add a per-group index, then stack the groups back together:

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Grouped = Table.Group(Source,{"Region"},{{"Rows",each Table.AddIndexColumn(_,"RankInRegion",1,1),type table}}),
    Combined = Table.Combine(Grouped[Rows])
in
    Combined

Table.Group hands each region’s rows to Table.AddIndexColumn, so the count restarts at 1 per region, then Table.Combine merges them.

The result numbers reps within their own region:

Region	Rep	RankInRegion
East	Tom	1
East	Sara	2
East	Priya	3
West	Omar	1
West	Lily	2

Notice the West rows restart at 1 instead of continuing from the East count.

Things to keep in mind with Table.AddIndexColumn

The index is static once added. It numbers the rows in their current order. If you sort or filter after adding it, the numbers stay attached to their rows and no longer run in order. Re-running the query after a sort renumbers from the new order, so add the index after you have set the order you want.
Without columnType the column is not a clean integer. Omit the fifth argument and Power Query assigns a general number type, which can load as a decimal. Pass Int64.Type (as in Table.AddIndexColumn(Source,"Index",1,1,Int64.Type)) when you want a true whole-number column downstream.
The increment can be negative or a decimal. It is just a number, so Table.AddIndexColumn(Source,"Countdown",100,-1) counts down and a decimal step like 0.5 is allowed. The count is not limited to going up by whole numbers.

Performance and query folding

Against a foldable source like SQL Server, Table.AddIndexColumn usually does not fold. Power Query cannot express a sequential row number as a native database operation, so it loads the rows and numbers them locally.

On large tables this means the source data is pulled in before the index is applied, so add it late in your query and after any filters that could have folded on their own.

Common questions about Table.AddIndexColumn

How do I start the index at 1?

Pass 1 as the third and fourth arguments, like Table.AddIndexColumn(Source,"Index",1,1). The third argument is the starting value and the fourth is the step.

How do I number rows within each group or category?

Group the table with Table.Group, add an index to each group’s sub-table, then Table.Combine the groups. Example 5 walks through the full pattern.

List of All Power Query Functions

Related Power Query Functions / Articles: