Rank column based on other text column

[PimK]

Hi all,

See below table, where I have a column Code (text) and a column Name. The aim is to rank the column Name based on the column Code. When column Name doesn't have a unique value, take the minimum of column Code. Column Code doesn't have duplicates. Like this:

Code	Name	Rank
01.00	D	1
01.01	D	1
01.02	A	2
01.03	B	3
01.04	D	1
01.05	B	3

How can I achieve this?

[v-stephen-msft · Accepted solution]

Hi @PimK ,

Since you don't want to change the Code column as number type. You need to first create column to return number type values.

Then create a column to return min code value group by name.

At last, create the ranking column.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[PimK]

Thanks Stephen!

[Greg_Deckler]

@PimK Maybe: 

Rank = RANKX( 'Table', [Name],,,Dense )

[PimK]

Thank you for the reply Greg.

The code you mentioned sorts on the column Name instead of the column Code.. 

The rank position for name A should be 2. Your code results in 3 as rank position for name A.