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.
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:
15 thoughts on “Create Row Number for Each Group in Power BI using Power Query”
Would be even better if there was a way to have an interface that was ANSI-99 thru ANSI-2003 “smart”. It would ask what you wanted to:
Apply calculation to Rows Preceding/Rows Following and how many (based on integer, a count, another query result, a percentage, a percentile, etc).
And so on and so forth 🙂
Not sure If I understand what do you mean, but I guess, you are talking about; it would be good to have the functionality of group by and partition by etc similar to the row-number function in t-SQL. which I agree for sure.
Thank you, this did the job I needed!
Very good solution
In my experience using a native query while working with large data set will increase the efficiency of data loading. I was trying to use SQL “row_number” function to derive the same results with partitioning option to start new numbering for each group, but failed. Is there a way to use row_number function to achieve the same results?
if the query you wrote that uses row_number T-SQL function failed, I suggest you investigate that query. Power BI just runs that query
great solution, thank you!
I am running this operation on a large amount of rows. When I open the Index column to expand the table, power bi is stuck “Loading Column Names….” i have waited for hours to no avail.
Let me know if anyone has a solution.
Best – robert
for a large number of rows, I recommend doing this on the data source. For example if in SQL Server database, then you can use row_number function in T-SQL
This solution fits my issue perfectly.
This is awesome – thanks!
Cool! Exactly what I needed, thank you!
Thanks. I am glad it helped
Thank you! I was looking for something like this since a long time.
Great instruction step by step, working for me as well! Thank you for sharing!