Duplicate records with same ID-using formula

[Anonymous]

Hi,

I have a table like below where I want the id of the duplicate records to be same.

Current Data                                      

Sno	Name	value
1	John	20
2	John	30
3	Jill	40
4	Kirk	50
5	Kirk	60

 Expected

Sno	Name	value
1	John	20
1	John	30
2	Jill	40
3	Kirk	50
3	Kirk	60

Thanks,

Ravi

[Eric_Zhang · Accepted solution]

Another approach in DAX.

RANK_SNO = 
RANKX (
    'Table',
    CALCULATE (
        MIN ( 'Table'[Sno] ),
        FILTER ( 'Table', EARLIER ( 'Table'[Name] ) = 'Table'[Name] )
    ),
    ,
    ASC,
    DENSE
)

[Anonymous]

Thanks Eric and Marcel , I have worked out both and works perfectly fine.

@Eric_Zhang  If I have the data without the S.no column  and  if I have the same Values for the same name in this case (John 20)would it be possible to acheive the same using RankX

, I used this but I am not getting the Correct output

Column = RANKX(Sheet1,
       
        Sheet1[Name]
    ,
,ASC,Dense)

Name	value
John	20
John	20
Jill	40
Kirk	50
Kirk	60

[MarcelBeug]

Steps:

1. Remove Sno,
2. Group By Name with operation "All Rows",
3. Add Index column (from 1) and give this column the name "Sno" (you can adjust the name in the generated code for the added index column),
4. Expand "value"  from the nested table (result from group by) and
5. Reorder the columns (I selected the columns in the desired order and then removed other columns (there are no other columns but this will result in reordering the columns in the sequnce of column selection)).

Code:

let
    Source = CurrentData,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Sno"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Name"}, {{"AllData", each _, type table}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Sno", 1, 1),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Added Index", "AllData", {"value"}, {"value"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded AllData",{"Sno", "Name", "value"})
in
    #"Removed Other Columns"