Forum Discussion

Lewdis_'s avatar
Lewdis_
Frequent Visitor
1 year ago
Solved

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...
Need Help
  • MNedix's avatar
    MNedix
    1 year ago

    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.

     

anmolmalviya05's avatar
anmolmalviya05
Super User
1 year ago

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

  1. In Power BI Desktop, go to the Home tab and click Transform Data to open the Power Query editor.
  2. Select your product table. and make a dupicate of it 
  3. 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.
  4. After that, go to the Add Column tab and click Index Column to create an ID column starting from 1.
  5. 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