Get the index of a row in Excel Power Query to condition the values in a new column (Are row indices accessible to the end-user?)

Question

I cannot access the index of a given, unknown table row without creating my own dedicated index column in Excel Power Query—the attribute seems to be hidden. Row indices would be particularly useful for conditioning the values of a new column based on the index (e.g. when creating a new column, row 0 is given a value of 0 and all other rows are given 1). If the unique values of a table row are known, getting the index is rather straightforward using Table.PositionOf(). However, this does not lend well (or at all) to randomized data. The documentation makes few references to table row indices, so it is seemingly inaccessible to the end-user for some reason. Oddly, I have run into a very similar problem of inaccessible attributes with VBA.

Example (following doc formatting)

Table.AddColumn(
    Table.FromRecords({
        [A = 1, B = 10],
        [A = 2, B = 20],
        [A = 3, B = 30]
    }),
    "C",
    each if [index value of row ("each") here] = 0 then 0 else 1,
    type number
)

Note: For my data, I do not know the values in columns A and B

The above example can be done in the three steps below: (but does it have to be?)

1. Create an index column Table.AddIndexColumn(Table_Reference, "Index")
2. Create your new column Table.AddColumn(Table_Reference, "C", each if [Index] = 0 then 0 else 1)
3. Remove the index column Table.RemoveColumns(Table_Reference, {"Index"})

Another method (a better solution than above) would be a two step process where I create a new column then assign specific values using List.InsertRange(). This function does have access to the row indices, but I have not figured out how to use it yet.

I am looking to either get the row indices during the column creation or use some other method to condition the values when they are generated based strictly on their position in the list. As for the insert method mentioned above, suggestions are welcome and would be appreciated, but are not real answers to the question.

Answer 1

Functionally in Power Query, you can do more or less whatever you want thanks to lists but performances might not always follow.

For directly creating a column with a specific value (ex: 0) in the first row then a different, identical value in all other rows (ex: 1), you can use this:

= Table.FromColumns(Table.ToColumns(PreviousStep) & {{0} & List.Repeat({1}, Table.RowCount(PreviousStep) - 1)}, Table.ColumnNames(PreviousStep) & {"NewColumn"})

For directly creating a column with a same value in all rows but the one corresponding to a given value in column "MyColumn" (value 14 in my example), you can use this:

= Table.FromColumns(Table.ToColumns(PreviousStep) & {List.ReplaceRange(List.Repeat({1}, Table.RowCount(PreviousStep)), List.PositionOf(PreviousStep[MyColumn], 14), 1, {0})}, Table.ColumnNames(PreviousStep) & {"NewColumn"})

With such examples, you have to define the column type in a second step. And I'd say that this has to be tested with respect to performances.