There’s an Index Column button in the Add Column tab of the power query editor. This will add a column containing a sequentially increasing integer. There are also options for which number to start the sequence with and how much to increment each row. If we add an index column starting at 1 and increment by 1 for each row, then this can be used as a serial number in our data. It’s a unique identifier for each row of data.
What if we have data for product orders and we want a unique identifier for each product in the list? Each product can appear multiple times in the data, but we want the same serial number for the product each time it appears. This is a bit trickier. In this post we’re going to explore how we can add grouped serial numbers to our data. In other words, a unique ID for each item in a list of potentially duplicated items. If you’re new to power query, you can check out this overview guide first.
Import the Data into the Power Query Editor
For this example, the data is in the workbook in a table named Data. We can import it into the power query editor by selecting the table and going to the Data tab and using the From Table/Range query command. This will take us straight into the power query editor.
Add an Index to Preserve Original Data Order
The steps we will perform later might change the original ordering of the data. In order to preserve this, we will immediately add an index column to our data. We can then sort the query using this column to restore our previous data ordering. This is an optional step and is only needed if you want to retain the ordering of the original data source.
Go to the Add Column tab and press the small arrow next to the Index Column command. This will show a menu were we can select to start the index From 1.
Group the Data by Product
Now we can group the data by Product. Select the Product column and then go to the Transform tab and press the Group By command.
In the Group by menu make sure Product is selected. Then for the Operation we are going to select All Rows and we can name this column Grouped Data.
This creates a column where each row entry is a table. The table in each row is the set of data for a given product and we end up with a row for each unique product. Test this out by clicking on one of the cells containing a Table element in the Grouped Data row. Click on the blank part of the cell and not the word Table. This will show a preview of the table at the bottom of the power query editor. We can see this is just the original data for the given product. Note that clicking on the word Table will add a navigation step to the query and navigate to that table.
Add a Unique Product ID with an Index Column
Now we are ready to add our product ID with another index column. Since the data is grouped by product this means the index will be unique by product.
Again, we need to go to the Add Column tab and press the Index Column command to create a new index column starting From 1. We can also rename this column to ID. Double click on the column heading to rename it.
Expand the Grouped Data Column
This is the key step. Expand the Grouped Data column in our query. This will essentially ungroup our previously grouped data.
Click on the expand toggle located to the right of the Grouped Data column heading. Select to Expand the tables in the column. Remove Product from the expanded selection since we already have a Product column in our grouped query. Deselect the Use original column name as prefix option to keep the column names less cluttered. We now have our unique grouped serial number.
Restore Original Order of the Source Data
Notice that after expanding our data, it’s no longer in the same order as the original source data was. The data has been sorted by product. This is where we can use the index column we added at the start of the process to regain the ordering.
Click on the Index column’s filter toggle found on the right side of the column heading. Choose the Sort Ascending option.
Clean Up the Query
We have accomplished our goal of adding a grouped serial number to our data, but we need to clean up our query a bit before we’re finished.
Remove the Index column. It’s served its purpose of getting back to the original sort order of the data and we no longer need it. Right click on the Index column heading an choose Remove. Change the data types of the remaining columns. We can change the Order Date to a date type, the Total to a currency type and the ID to a text type. To change a column’s data type click on the data type icon in its column heading and select the appropriate option. Move the ID column to the first column position. Click and drag it over the its new location. Now we can close and load the query to an Excel table in our workbook. Go to the Home tab and press the Close & Load button to load the table into an Excel workbook.
Conclusion
We were able to add a unique serial number by product fairly easily with power query. The main idea was to group the data by product without any aggregation, then add an index column and then ungroup the data. This produced a sequential ID number which is the same for each product. We were also able to preserve the original data’s ordering by adding an index column before our operations and then sorting on this index afterwards. The cool thing is that we can now add new products into our data and when we refresh the query, it will also get a unique serial number.
