Power BI – Group By Index

By: David Rohlfs

Introduction

Recently I have come across a problem where I needed to have an index column that restarted for each separate category. It turns out that there isn’t a built-in way you can do this without creating multiple queries. But this blog will take you through the process of creating an index in a single query.

This method is similar but not the exact same as a method developed by Radacad. If you are interested in seeing their blog on this subject, here is that link: https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

This blog will assume that you already have basic knowledge on how an index function works in Power Query. If you need to cover this, there will be a link at the bottom of the page of an EPM Strategy blog about the Index Function.

In this blog we will cover when you can use this method, some common problems that may occur, and a walkthrough describing this process.

 

When to use it

Creating an index that can restart is very helpful in many situations. You can use it to group together a set of rows, give a defining value for each row, or use it to refer to a future/previous row.

What I mean exactly by this process is that you can base your index off a column and not off the table. Here is an example of what the result is supposed to be:

If you are using a grouped index column, then you might need to create a row specific column. The easiest way to do this is to combine your new index column with the column that you grouped by. Doing this right after you create your index will give you a column that is row specific and contains no duplicate values.

 

Common Problems

The most obvious problem that may happen to you is that your index is not in the correct order. You may need to do a sort by one or multiple columns to get the desired index order on each of your rows.

 

Walkthrough

In this walkthrough we are going to be grouping and indexing a set of values based on a category. This process uses multiple simpler functions in Power BI. If you see or use something that you are not familiar with, most of the processes used are linked below in other EPM Strategy blogs.

To start, here is what my sample data looks like:

I want to do my group and index based on the Product column. This will mean that each time my product changes, my index restarts. To start, I need to go to the Group By button in the home or transform ribbon.

In the Group By pop-out, I am going to change the name of my new column and change the operation to be an All Rows operation. This will be important to keep our existing table in a future step. Once you do this you can go ahead and click OK.

This is what my result looks like:

To do an index, we are going to create a custom column. Go to the Add Column ribbon and select the custom column function.

This is what my code looks like. Notice that I am using my All Rows as the table name. We can do this because our All Rows column’s type is a table.

Now we can delete our All Rows Column. We don’t need this because all our data is stored inside of our new “Custom” column. Just select the All Rows column and click on the remove columns function.

Now we can expand our Custom Column. Remember that we still have our Product column, so you don’t need to expand this column. Also, you probably want to uncheck the “Use original column name as prefix” box at the bottom of the dropdown. Once you do this you can click OK in the bottom right corner of this dropdown.

Now you should see your resulting Index Column. In here you can scroll through and check that your index restarts when the Product Column changes values.

Once you do this remember that you will need to change the types of your columns.

 

Conclusion

While it can be easy to create a normal index, creating a grouped index isn’t as simple until you learn how to. I hope that you learned something new in this blog and if you did, there are several other EPM Strategy blogs linked below that are similar or complimentary to group by and index.

 

Links to Related Articles:

Row Specific Columns

Group By

Group By All Rows

Remove Duplicates

Index Function in Power Query

Adding A Custom Column

Remove Duplicates from Multiple Columns

Utilizing a Group by Function

Comments