How to add a Power BI Index Column and Ways to Use One

Adding an Index Column in Power BI in Power BI is quick and easy using the Power Query editor. Index columns can be an effective way to split data transformation into multiple steps, sort data, and identify individual rows in subsequent DAX formulas.

Users can create more dynamic index columns using Power BI groups to assign a specific index number to a summarized dataset and join it back to the original data.

Learn how to create an index column in Power BI, customize the index column, and create a dynamic index column based on grouped data.

We’ll look at how to create an index Column in Power BI, create in index for grouped data, and explain several of the most popular use cases.

Let’s take a look!

What are Power BI Index Columns?

Index columns are a method to assign numeric values to each of the rows in a Power BI dataset. Even though Power BI automatically shows you the row number that a specific value is listed on, these row values cannot be modified and exist only for reference purposes.

Index columns can be created starting with a 1 or a zero. They can also be customized to start with any number and have any defined number of values in between each newly generated row value.

Screenshot of a Power BI index column being added to a dataset

For example, if you want to skip every 5 rows, 10 rows, or other increment an index column in Power BI can be created to do so.

Reasons to Use an Index Column in Power BI

Index columns are an incredibly useful data transformation tool. While there are a wide range of use cases to add an index column there are a few specific ones that come up more often than others.

Here are some example use cases of index columns:

  • Transforming Data in Batches – Sometimes it is easier to transform data in sections than it would be to try and modify an entire dataset all at once. For example, a bank statement may include header, row level detail and subtotal information. Add an index column, then filter the bank statement into 3 sections. You can later union or append the three sections back together for one continuous dataset based on the row ID.
  • Creating Commonalities to Merge – Datasets do not always have a common field to merge off of. By adding an index column, you can force Power BI to merge or join datasets based on their row position.
  • Sequencing Visuals – The way that you wish to present data in a Power BI visual may not fit an alphabetical or numeric sequence. Try creating an index that includes a row sequence and the sort order of different line items. You can then merge them and sort by the index column forcing the sort order of your reporting lines.

We’re sure that there are many other great use cases you will discover when adding index columns to your reports.

Let’s take a look how to do it!

How to Add an Index Column to Power BI

To create an Index Column in Power BI, first launch the Power Query editor. Then navigate to Add Column, click on the Index Column dropdown menu and then select whether you want the new index column to start From 0, From 1 or Custom. Custom Index Columns give you the ability to define the starting value along with the increment value that is skipped in between each newly created row.

Here are the steps in more detail.

Step 1.) Launch the Power Query Editor. The fastest way is to right click on a table you want to modify in the report viewer and select Edit Query.

Launching the Power BI Power Query editor to transform an incoming data model

Step 2.) From the Power Query Editor, Navigate to Add Column, and Select Index Column from the drop down menu.

You will see 3 options.

  • From 0 – Starts the new index rows with the first row as Zero
  • From 1 – Starts the new index rows with the first row as One
  • Custom – Lets you select any starting number and any numeric increment in between rows.
Creating an index column from 0, from 1, or from a custom starting point

Step 3.) The index column has been generated. It default generates to the far right side of the dataset. You can drag the column to the left by holding down the left mouse button on the Index column name and moving it manually.

The example below started the new index column From 1

Example of an index column starting with 1 in Power BI

Note: If you want to modify the starting index or the increment from one row to the next, create a custom Index Column. You can define the starting Index and the Increment.

Customizing an index column increment in Power BI

Increments can be any whole number, and it’s possible to start the index column based any number that you choose.

How to Add an Index Column Based on Group in Power BI

To add an index column based on a group in Power BI there are several steps to follow. The overall concept is to duplicate an existing table, group it, assign an index column to it then merge it with the original data table. Once the merged table is expanded the index column will be replicated for each occurrence of the row value.

Here’s a further outlined set of instructions.

The following screenshot is what the new table should look like, after duplicating, grouping and adding an index column.

Screenshot of an index column being assigned to grouped and summarized data in Power BI

Next, Merge the two Power BI tables together.

Use the column that you initially grouped by as the common criteria.

When performing a merge in Power BI, the values in the second index table will be replicated for each occurnace of a row value in the first table.

Screenshot of merging grouped and indexed data against a starting table

The results of the newly merged table will show a new index column that has been joined in with the first dataset.

The resulting data table after a dynamically grouped index table is merged with the starting data set.

This technique is especially useful when creating index by group. It’s dynamic and easy to setup.

The primary drawback is that you are forced to duplicate a table which may not be practical in all datasets. It may be more practical to issue a separate query into a SQL database to summarize a table prior to importing it.

Another method could be to create a manual index table using Excel, even though you would lose the dynamic nature of the grouping.


Index columns are a valuable tool for transforming data in Power BI. An index column can quickly be added from the Add Columns tab of the Power Query Editor. To make index more dynamic, you can create a summary table prior to assigning an index then join the summary table against the original table.

While it’s possible to create an index column using DAX, it is not recommended due to the time it takes to execute a DAX query against the dataset. Power Query will pre-load an index column during a data refresh making a better user experience for the report viewer.

Scroll to Top