Create Row Number for Each Group in Power BI using Power Query

If you want to create a row-number statically as a pre-calculation in Power BI, then Power Query has a very simple way of doing it; Add Index Column. However, the Index column creates the row number regardless of any grouping or categorization. sometimes you want to take that into account too. There is a little trick that you can add an index column with the grouping, read the rest of this blog post to learn that method.

Add a Row Number using Add Index Column

In Power Query Editor, You can add an index column easily through the graphical interface of Power Query Editor.

There are a few options when you add the index column. the index can start from zero (default), or one, or you can select the custom starting point and the seed. here is an example, the index started from one:

If you are just after a simple row number, then the above gives you what you want. but if you are after a row number by group, then you need a bit more steps to do.

Row Number By Group

If you want your index to restart at the beginning of every quarter, then you are after something like below:

As you can see the index is restarting at the beginning of every group. Let’s see how you can do that easily with a few steps.

Before I explain the method to you: there are multiple ways of getting the row number based on a group. Some of them require writing Power Query scripts, and to be fair some of them are faster than the method I would explain here. But if you do not have so many data rows, and you want an easy solution, I do recommend the below approach. I might write about other methods later in other blog posts.

Step 1: Group the data

The first step is to Group the data, right-click on the field that you want to be your grouping field, and select Group By.

Then you will see the Group By the window, you can even group by multiple fields if you want with holding the Ctrl and selecting multiple fields, or using the Advanced tab here. for the new column to be created using the Group By, select the Operation to be All Rows. Column Name doesn’t matter.

Selecting All Rows means no aggregation from the group by, instead, it will populate a sub table from the original table for each group. If you click on a blank area of a cell that contains Table in it, you can see the sub-table in the preview pane underneath.

Step 2: Add Index to Sub-Table

Now that you have the sub-table of each group, you can add the index column to the sub-table. However, there is no graphical user interface for that at the moment. You can do it this way: Add a Custom Column:

Then using the Table.AddIndexColumn function in Power Query, you can add the index to the sub-table.

Table.AddIndexColumn([Count],"Index",1)

The [Count] in the expression above is the name of the column in my table that has sub-tables in it, if your column name is different, you should be selecting that from the list.

This newly created column now has sub-tables but with one extra column: Index

Step 3: Expand

Now you can remove all other columns and only keep the last column you have created.

You don’t need any of those columns, because even the group column (type in the example above) are in the sub-table.

Then expand the column:

Optional: Change the Data Type

Usually, after expand, columns get the ANY data type, you can select all columns (using Ctrl+A), and then under Transform, use Detect Data Types.

The Detect Data Type, automatically set the data type of all columns simply.

and here is the final results with the Index column added:

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.