Power Query: Create row number for each group in Excel

If you've ever needed to create sequential row numbers for each group of data in Excel, you know that while a formula can solve the task, there is a more efficient way using Power Query. In this article, we'll show you how to use Power Query to easily add row numbers within each group, streamlining your workflow and saving time when working with grouped data in Excel.

Power Query: Create row number for each group in Excel

To generate the index numbers for each group of the data, the Power Query can do you a favor. Please do with the following steps:

1. Select the data range that you want to insert the row number. In Excel 365, click "Data" > "From Table/Range", see screenshot:

Note: In Excel 2016 and newer versions, click "Data" > "From Table", see screenshot:

2. In the "Create Table" dialog box, click "OK" button to create a table, see screenshot:

3. And then, in the opened "Power Query Editor" window, select the column that you want to use to group the table, then right click it. And then, choose "Group By" from the context menu, see screenshot:

4. In the "Group By" dialog box:

• Select the column name that you want to insert the row number based on from the drop-down list;
• Select" All Rows" option from the "Operation" drop-down list;
• Click "OK" button.

5. Now, return to the "Power Query Editor" window, click "Add Column" > "Custom Column", see screenshot:

6. In the "Custom Column" dialog box, using the "Table.AddIndexColumn" function as below screenshot show:

• Type a new column name into the" New column name" text box;
• Then, enter the following formula into the "Custom column formula" box;

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

• At last, click "OK" button.

Note: In the above formula, "[Count]" is the name of the column you have created in step 4, please change it to your need.

7. Now, return to the "Power Query Editor" window, you should remove all other columns and only keep the last column you have created. Right-click the name of the last column you created, select "Remove Other Columns", see screenshot:

8. Then, expand this column, click the "Expand" icon next to the column title. In the expanded box, check "Expand" option, and then check all the column names from the list box, and uncheck the Use original column name as prefix option. And finally, click "OK" button. See screenshot:

9. And now, you can see the row index numbers are displayed as below screenshot shown:

10. Then, please click "Home" > "Close & Load" > "Close & Load" to load the data into a new worksheet, see screenshots: