NEW! FREE 90 Minutes Excel VBA Course for Beginners! Watch now 

Power BITutorials

How to Create Grouped Serial Numbers In Power Query

2 Mins read

Steps 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.

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.

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 :

To get in-depth knowledge on Power Query in Excel & Power BI along with its various applications, you can enroll for live Power Query Online training with 24/7 support and lifetime access.

Related posts
Power BITutorials

Opportunity Analysis Power BI Dashboard

1 Mins read
Information: A software company that has two sales channels: direct and partner. The sales manager created this dashboard to track opportunities and…
Power BITutorials

Procurement Analysis Power BI Dashboard

1 Mins read
Information: It analyzes a manufacturing company’s spending on vendors by category and location. we explore these areas: Who the top vendors are…
Power BITutorials

Retail Analysis Power BI Dashboard

1 Mins read
Information: It analyzes retail sales data of items sold across multiple stores and districts The metrics compare this year’s performance to last…