Automatically Create Unique Product ID

[Anonymous]

Hello all...

I'm hoping someone can help me understand how to create a calculated column that creates a unique Product ID for each item in my source data. My data contains multiple rows for each product. I'm thinking I need to use RANKX and/or the Index function in Power Query.

Thanks in advance to anyone who can help!

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

If you want to generate the unique ID like following,

Item	Product ID
Apple	1
Apple	1
Banana	2
Banana	2
Candy	3
Candy	3

We can create a calculated column as following:

Column = 
RANKX ( 'Table', 'Table'[item],, ASC, DENSE )

But if you want a distinct ID for each row, like following table , we can add an index column in Power Query Editor:

Item	Product ID
Apple	1
Apple	2
Banana	3
Banana	4
Candy	5
Candy	6

Best regards,

[mwegener]

Hi @Anonymous ,

you can add an Index Column in Power Query

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[JarroVGIT]

Adding a calculated column with a RANKX() would do the trick I think, but the product ID could change upon data refresh of course.
CalcCulmn = RANKX(table, Table[item],,ASC, skip)