Need Help? Chat with us
[email protected] +1 203-349-9909 / +91-8080042523

How to Create Grouped Serial Numbers In Power Query

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.

BEFORE CREATING GROUPED SERIAL NUMBERS

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

GROUP DATA BY PRODUCT NAME

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.

AFTER GROUPING DATA BY PRODUCT NAME

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.

, How to Create Grouped Serial Numbers In Power Query

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.

AFTER EXPANDING GROUPED DATA COLUMN

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.

 AFTER REARRANGING ID COLUMN

You can learn more :

Leave a Reply