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: