How to Add Index Column Using Power Query Editor in Power BI

When we are working on a Power BI report for a client, one of my colleagues asked me a question like this:

I want to create an index column, but the index should be based on the values of another column.

This is what the index column shows now:

But this was not the expected result. They wanted the index column to be like this:

This means the same Department should have the same index number, and the index value should increase only when the department changes.

We can achieve this by using the Group By and Index functionality in the Power Query Editor in Power BI.

In this tutorial, I will explain how to add an Index Column using Power Query Editor in Power BI. Also, I will cover:

• Add a custom index column using Power Query Editor in Power BI
• Add an index column based on the values of another column using Power Query Editor in Power BI

Add Index Column Using Power Query Editor in Power BI

For this example, I have loaded data into Power BI Desktop that contains the following columns and values:

Now, follow the steps below to add an index column in Power BI:

1. Open Power BI desktop and load your data as mentioned above. I uploaded the above data.
2. Next, under the Home tab, click the Transform data to open the Power Query editor.

3. In the Power Query editor, go to the Add column tab under the General section, expand the Index Column.

4. Then click From 1 to add the index column starting from 1. If you want the index column to start from 0, you can click From 0.

This way, we can add the Index column in Power BI using Power Query Editor.

Add a Custom Index Column using Power Query Editor in Power BI

Now I do not want the index column to start from 0 or 1. I want the index to start from a specific number, like 100, or increase by a custom value. To do this, we can create a custom index column in Power BI using the Power Query Editor.

Follow the steps below:

1. Open Power BI Desktop and go to Transform data to open the Power Query Editor.
2. In the Power Query Editor, go to the Add Column tab and click Index Column. From the list, select Custom.

3. In the Add Index Column pop-up:
   • Set the Start value (for example, 100)
   • Set the Increment value (for example, 2)
4. Click OK to apply the changes.

This will add a custom index column with the start value and increment you specified.

Using this method, we can fully control how the index column is created in Power BI using the Power Query Editor.

Add an Index Column Based on the Values of Another Column using Power Query

In this scenario, we want to create an index column based on the values of another column. This means the same Department should have the same index number, and the index should increase only when the department changes.

For this example, I loaded the save data I mentioned in the introduction.

Now follow the steps below:

1. In the Power BI Desktop, load your data, then open the Power Query editor.
2. Next, go to the Home tab and click Group By.

3. In the Group By pop-up:
   • Select Department as the column to group by
   • Add a new column name (for example, AllData)
   • Choose All Rows as the operation
4. Click OK.

Now, each department will be grouped together. Next, add an index column to this grouped table.

5. Go to the Add Column tab, click Index Column, and select From 1.

6. After that, expand the grouped column to bring back the original rows.

Once you expand the column, you will see that all rows with the same Department have the same index number.

This is how we can add an index column based on the values of another column using Power Query Editor in Power BI.

In this tutorial, we learned different ways to add an index column in Power BI using the Power Query Editor. First, we saw how to add a regular index column starting from 0 or 1. Then, we learned how to create a custom index column by choosing our own start value and increment. Finally, we covered how to create an index column based on the values of another column.

You may like the following Power BI tutorials:

• Change Data Type in Power BI
• Sort Slicer by Another Column in Power BI
• Remove Filters in Power BI DAX
• Add an Empty Column in Power BI
• Count Data With Multiple Filter Conditions in Power BI DAX