Forum Discussion
Create ID from values in column
I have a dataset with product category as one column in the product dataset. Since i have a lot of products this column is for each of them.
Is there a simple way to give them and ID number from 1 to ... depending on how many unique combinations this column has?
Example Bike will be 1 Accessories 2 et
| Category |
| Bikes |
| Accessories |
| Clothing |
Heya,
For this kind of data (e.g. Products) it is adviceable to have a dedicated table with unique values so you can easily manage relationships and other calculations. If you don't already have one, then you can create a Products table using the code below (assuming that 'Table' is your dataset table):
Products = DISTINCT(SELECTCOLUMNS('Table',"Products",'Table'[Category))Then, you can simply add a calculated index column with the code:
Index = RANKX(ALL('Products'),'Products'[Products],,ASC)Hope it helps.
3 Replies
Hey Lewdis_ ,
in Power Query there is the function of an Index column.
But you would have to match the index then also in the fact table.
What is your data source? It would be a lot better if you could do it there, for example in the underlying relationan database.
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Connect with me: LinkedInHi Lewdis_ , Hope you are doing good.
You can achieve this using power query editor in Power BI.you can assign unique IDs using Power Query.
- In Power BI Desktop, go to the Home tab and click Transform Data to open the Power Query editor.
- Select your product table. and make a dupicate of it
- Select the Category column, then go to the Transform tab and click Group By.
- In the Group By window, group by the Category column and add an operation to Count Rows. This will give you a table with the unique categories.
- After that, go to the Add Column tab and click Index Column to create an ID column starting from 1.
- Now, merge this table with your original table based on the Category column, bringing the unique IDs back into your main dataset.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in
Heya,
For this kind of data (e.g. Products) it is adviceable to have a dedicated table with unique values so you can easily manage relationships and other calculations. If you don't already have one, then you can create a Products table using the code below (assuming that 'Table' is your dataset table):
Products = DISTINCT(SELECTCOLUMNS('Table',"Products",'Table'[Category))Then, you can simply add a calculated index column with the code:
Index = RANKX(ALL('Products'),'Products'[Products],,ASC)Hope it helps.
