Create new numeric column from Categorical column

[Janaya1]

Is there a way to create a new column from a categorical column that acts like a numerical reference that is unique for that category without creating an if then statement that lists each category or a map? I have hundreds of different categories in a column that need unique numbered categories. I think this shouldn't be difficult but maybe I'm just overthinking it?

For example:

starting table

Category	Value
A	70
A	64
B	26
A	58
C	49
D	26
A	06
B	13
D	46
C	18

resulting table

Category	Value	Categorical Value
A	70	1
A	64	1
B	26	2
A	58	1
C	49	3
D	26	4
A	06	1
B	13	2
D	46	4
C	18	3

[techies · Accepted solution]

@Janaya1 please try this calculated column

Categorical Value =

VAR CategoryIndex =

    RANKX(

        ALL(Sheet1[Category]),  

        sheet1[Category],      

        , ASC, DENSE              

    )

RETURN CategoryIndex

[Janaya1]

thank you!