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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

3 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:

    Group By
    Partition By
    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 🙂

    • Hi Steven
      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.
      Cheers
      Reza

Leave a Reply

%d bloggers like this: