Table.AddIndexColumn

Updated on

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

Description

Appends a column named newColumnName to the table with explicit position values. An optional value, initialValue, the initial index value. An optional value, increment, specifies how much to increment each index value.

Examples

Add an index column named “Index” to the table.

Table.AddIndexColumn( 
    Table.FromRecords( {
        [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
        [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
        [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
        [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
    } ),
    "Index"
 )

 /* Output: 
Table.FromRecords( {
    [CustomerID = 1, Name = "Bob", Phone = "123-4567", Index = 0],
    [CustomerID = 2, Name = "Jim", Phone = "987-6543", Index = 1],
    [CustomerID = 3, Name = "Paul", Phone = "543-7890", Index = 2],
    [CustomerID = 4, Name = "Ringo", Phone = "232-1550", Index = 3]
} )
 */ 

Add an index column named “index”, starting at value 10 and incrementing by 5, to the table.

Table.AddIndexColumn( 
    Table.FromRecords( {
        [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
        [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
        [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
        [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
    } ),
    "Index",
    10,
    5
 )

 /* Output: 
Table.FromRecords( {
    [CustomerID = 1, Name = "Bob", Phone = "123-4567", Index = 10],
    [CustomerID = 2, Name = "Jim", Phone = "987-6543", Index = 15],
    [CustomerID = 3, Name = "Paul", Phone = "543-7890", Index = 20],
    [CustomerID = 4, Name = "Ringo", Phone = "232-1550", Index = 25]
} )
 */ 

Learn more about Table.AddIndexColumn in the following articles:

Other functions related to Table.AddIndexColumn are:

BI Gorilla Youtube Channel

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