Table.AddIndexColumn
Adds a column that numbers the rows, from 0 by default, or from a start value and step you choose.
Table.AddIndexColumn(table as table, newColumnName as text, optional initialValue as nullable number, optional increment as nullable number, optional columnType as nullable type) as tableReturns: The table with a numbered column.
What it does
Table.AddIndexColumn writes a sequence number beside each row, such as 0, 1, 2 and so on. A numbered source row is useful for audit trails: a reviewer can point to "line 214 of the bank file" and find it again. The numbers follow the order the rows are in at the moment of the step.
Syntax and arguments
- table: the table to number. Required.
- newColumnName: the name of the new column. Required.
- initialValue: the first number. Optional, and the default is 0.
- increment: the step between numbers. Optional, and the default is 1.
- columnType: the data type of the new column, such as
Int64.Type. Optional.
Example
A bank export has been cleaned and its headers promoted. Each row is numbered from 1 so the reviewer can match a line to the original file.
let
Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
Numbered = Table.AddIndexColumn(Source, "SourceLine", 1, 1, Int64.Type),
Selected = Table.SelectColumns(Numbered, {"SourceLine", "Date", "Description", "Amount"})
in
Selected
The first data row gets 1 and each row after it gets one more. Keeping the numbers in the query means the reference survives every later step that does not reorder the rows.
Things to watch
- The numbers follow the current order. If you sort the table later, the numbers stay with the rows they were given, so they will no longer be in order. Add the index after the sort if you want the two to match.
- Put the index after the steps that filter rows that should not count. Otherwise the numbers have gaps.
- Power Query does not fold this step to SQL. Put it after the filters that do fold, so the database does the heavy work first.
- Some steps, such as joins and grouping, do not keep row order. Add the index before those steps if you need the source order.
In business terms
An audit trail needs to show where each line came from. A source line number in the working file lets the reviewer tie a figure in the report back to the original document, which is what an auditor will ask for.