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.
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!
Table of Contents
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.
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.
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.
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
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.
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.
- Duplicate the starting table.
- Group the New Table
- Add an Index Column
The following screenshot is what the new table should look like, after duplicating, grouping and adding an index column.
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.
The results of the newly merged table will show a new index column that has been joined in with the first dataset.
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.