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 tableDescription
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]
} )
*/
Related articles
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
Related functions
Other functions related to Table.AddIndexColumn are:
