Identifying duplicates

[83dons]

Hi I have a simple table with two columns and I want to identify all the roles where there are duplicate NI Numbers (ie NI Number > 1). I guess there could be more than 2 duplicates for the same NI Number at some points.

ID      NI_Number

0001   JE000001B

0002   SE000001C

0003   JE000001B

0004   FE0000001H

0005   HR000002F

0006   AD000001D

0006   AD000001D

0007  SD000001X

This would display only rows where NI Number has duplicates in the original column. I think there may be duplicates in the ID column but that is ok.

ID      NI_Number

0001   JE000001B

0003   JE000001B

0006   AD000001D

0006   AD000001D

Thanks again.

[Demert · Accepted solution]

Hi @83dons  if you have access to edit these table in Power Query, you have a nice option to only keep duplicates. 

Select the column where you want to keep the duplicates from then select keep rows and click on keep duplicates.

[83dons]

Perfect thanks! Very easy!

[Ahmedx]

you need to create a table, pull out the NI_Number column into the rows and write a measure like this

 = DISTICTCOUNT( 'yourtable'[ID])

if the measure shows more than 1 then there is a duplicate
 

[83dons]

@Ahmedx that makes sense but not sure how to do that practically.