If we have data for products and we want a unique identifier for each product name in the list. But as you can see below each product appears multiple times in the data table.
Create Grouped Serial Numbers In Power Query
But we want to keep the same serial number for the product each time it appears. This user requirement can be fulfilled by Power Query very easily.
Follow the below steps to achieve your goal-
Step 1: Group Data by Product Name
To group data by Product Name in Power Query, go to-
Transform > Group By
Step 2: Preview Table at the Bottom of the Power Query Editor
When you group by the Product Name column it creates a column where each row entry is converted to a table. The table in each row is the set of data for a given product. The complete row consists of data for unique products. Click on one of the cells containing a Table element in the Grouped Data row.
NOTE: 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 as shown in the picture below.
Step 3: Add Index Column to the Grouped Data
Now, it’s time to add an index column to the grouped data. Since the data is grouped by product name, this means that the index will be unique by product name. Data table will look as shown below after adding Index column.
Step 4: Expand as Grouped Data Column
Expand the Grouped Data column in the query to ungroup the previously grouped data. Click on the expand toggle button located on the right of the Grouped Data column heading for expanding. Also, rename the Index column as ID column since we are using it for generating ng ID. The picture below shows the expanded data.
Step 5: Rearrange the ID Column
Rearrange the ID column by dragging it to the first position as shown in the picture below. The ID remains unique for each type of product name irrespective of their occurrence.
You can learn more :
- How to Edit A Query using Power Query
- Learn How to Split Queries In Power Query
- Manage Parameters in Excel
- Creating A List Query From A Table Column