Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 ...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    Another approach in DAX.

     

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

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

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"