Table.AddIndexColumn - Table Function

Table.AddIndexColumn is a Power Query M function that appends a column to a table with explicit position values, starting from an optional initial value and incrementing by an optional value. The function returns a modified table with the new index column added.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
table		The table to add an index column to.
newColumnName		The name of the index column.
initialValue	optional	The starting value of the number sequence.
increment	optional	Indicates the size of each step within the sequence.
columnType	optional	Ascribes a data type to the newly created column.

Description

Table.AddIndexColumn adds a new column to a table that contains a sequence of numbers. You can customize the starting value, increment, and data type of the numbers. Alternatively, if you leave out the optional arguments, the default settings use an index that starts at 0, increments by 1, and uses a decimal number data type.

Examples

Adding an index column to a table in Power Query can be very helpful. It assigns a number to each row, making it easier to track where in the table the row comes from. Let’s explore how to add an index column step by step using the Table.AddIndexColumn function.

Adding an Index Starting at 0

Imagine you have a table named Source, but it doesn’t have a way to clearly identify each row:

Table.AddIndexColumn original table to add index to in Power Query M

You can fix this by adding an index column like this:

Table.AddIndexColumn(
  Source,             // The table to add the index column to.
  "Index-0-1",        // The name of the new index column.
  0,                  // The starting value for the index column.
  1,                  // The increment value for the index column.
  Int64.Type          // The data type of the new index column.
)

What This Does:

Source: The table you’re adding the index column to.
"Index-0-1": This is the name of the new index column.
0: The index starts at 0.
1: Each row number increases by 1.
Int64.Type: The numbers are stored as whole numbers.

Here’s what it looks like:

Table.AddIndexColumn Add Column Starting At Zero Incrementing By One in Power Query M

Creating a Custom Index with Different Start and Step Values

If you need the index to start at a number other than 0 or increase by more than 1, you can customize it. For example:

Table.AddIndexColumn(
  Source, 
  "Index-5-5", 
  5, 
  5, 
  Int64.Type
)

So what does this do?

The index starts at 5.
Each row increases by 5.

The result of adding these two columns is:

Table.AddIndexColumn Add Column Starting At Five Incrementing By Five in Power Query M

Using Default Settings for Simplicity

The Table.AddIndexColumn function has optional arguments, which means you don’t need to specify all of them. For example, you can use just the table and column name:

Table.AddIndexColumn(
  Source, 
  "Index-default", 
)

So, what happens with default settings?

The index starts at 0.
It increases by 1 for each row.
The data type is type number (decimal).

Here’s what that looks like:

Table.AddIndexColumn without optional arguments in Power Query M

Try It Yourself

You can try these examples yourself by pasting below code in Power Query’s advanced editor:

let
  Source = 
    #table(
      type table [ StepName = text, TaskDescription = text, DurationMinutes = Int64.Type ],
      {
          { "Initialization",   "Set up the project environment", 10 },
          { "Data Collection",  "Gather necessary data",          30 },
          { "Data Cleaning",    "Clean and preprocess data",      25 },
          { "Modeling",         "Build the data model",           40 },
          { "Validation",       "Validate the model",             20 },
          { "Deployment",       "Deploy the model",               15 },
          { "Monitoring",       "Monitor deployed model",         15 }
      }
    ),
    AddIndex_0_1 = Table.AddIndexColumn( Source, "Index-0-1", 0, 1, Int64.Type ),
    AddIndex_5_5 = Table.AddIndexColumn( Source, "Index-5-5", 5, 5, Int64.Type ),
    AddIndexDefault = Table.AddIndexColumn( Source, "IndexDefault" )
in
    AddIndexDefault

Learn more about Table.AddIndexColumn in the following articles:

Get Value from Previous Row using Power Query
This article shows how to get the previous row value using Power Query. You can adjust the 3 ways to also retrieve an earlier or later row. » Read more

Other functions related to Table.AddIndexColumn are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-addindexcolumn