Downloads
Power Query MTable: columnsIntermediate

Table.AddIndexColumn

Adds a column that numbers the rows, from 0 by default, or from a start value and step you choose.

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

Returns: 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

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

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.

Related functions

All Power Query M functions · View as Markdown · M basics