In this article, we will show you how to add an index column in Power Query, what they can be used for, as well as how to create an index column by group (based on another column).
How do I add an index column in Power Query?
To add an index column to your table in Power Query, you need to navigate to the ‘Add Column’ section in the top ribbon and choose ‘Index Column’.
Here’s what it looks like in Power Query:
You can choose to add an index column starting from row 0 or row 1. There is also an option to choose ‘custom’ so you can choose where the index starts from and the increment.
We have an example to show you what an index column looks like in Power Query.
Here’s the example data:
If we choose to add an index column by following the instructions above and choose ‘From 1’, this is the result:
One thing to note when using an index column in Power Query is that it will apply based on the data in the previous step.
If you are sorting any columns in your table, this is something to be aware of and whether you add an index column before or after.
For example, we can sort our data by ‘Product’, as below:
If we add an index column now, you will notice that the numbers are different in relation to the order number, because we have applied the above sort.
Creating an index column by group
When we create an index column by group, we are basing the index on another column or certain criteria.
We will use the same example data from the previous section to show how to create an index column based on a certain column.
We want to base our index column off the ‘Product’ column, where each individual product is one increment.
Firstly, we need to Group By this column (Product) and choose ‘All Rows’.
Then, we create an index column as shown in the above section, starting from 1 with an increment of 1.
This has now grouped the products together and added an index against them.
All we need to do now is expand the columns, excluding Product, to bring back the rest of the original data.
Now we have created an index column by group (based on the product column).
Here is the result:
