Forum Discussion
Duplicate records with same ID-using formula
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
Another approach in DAX.
RANK_SNO = RANKX ( 'Table', CALCULATE ( MIN ( 'Table'[Sno] ), FILTER ( 'Table', EARLIER ( 'Table'[Name] ) = 'Table'[Name] ) ), , ASC, DENSE )
3 Replies
Steps:
- Remove Sno,
- Group By Name with operation "All Rows",
- 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),
- Expand "value" from the nested table (result from group by) and
- 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"Another approach in DAX.
RANK_SNO = RANKX ( 'Table', CALCULATE ( MIN ( 'Table'[Sno] ), FILTER ( 'Table', EARLIER ( 'Table'[Name] ) = 'Table'[Name] ) ), , ASC, DENSE )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
