Identifying duplicates in one column

[83dons]

What is the simplest way of listing the duplicates in a single column? StaffID is unique but NInumber may not be hence we need to check for duplicates on it.

Table looks like

StaffID NInumber

0001   AS12345G

0002  AS12345G

0003  HY4353554M

0004  LI83243234C

0005 AW7345345N

Therefore the output would be:

StaffID NInumber

0001   AS12345G

0002  AS12345G

Thanks again for any help.

[Anonymous]

Hi @83dons,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are trying to way to list all the duplicate values in a column. As @burakkaragoz and @johnt75 have already responded to your query, please go through their response and check if it solves your issue.

I would also take a moment to thank @burakkaragoz and @johnt75, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[burakkaragoz]

Hi @83dons ,

Easiest way is to use a calculated column to flag duplicates, then filter. Here’s how you can do it in Power BI using DAX:

1. Add a new calculated column:

dax

IsDuplicate = CALCULATE(
    COUNTROWS('YourTable'),
    FILTER(
        'YourTable',
        'YourTable'[NNumber] = EARLIER('YourTable'[NNumber])
    )
) > 1

1. Then, just filter your table (or visual) where [IsDuplicate] = TRUE.
   This will show all rows where NNumber is duplicated, just like your output.

Let me know if you want a solution in Power Query or SQL as well!

[83dons]

Hi @burakkaragoz I dont really use DAX is there a way to do this using the power BI ribbon at the top? Or can I just paste it into a transform step after the =

[burakkaragoz · Accepted solution]

@83dons ,

No worries! If you’re not comfortable with DAX, you can do this easily in Power Query as well—no formulas needed.

Here’s how:

1. In Power Query, select the NNumber column.
2. Go to the “Group By” option in the toolbar.
3. Group by NNumber, and add an “All Rows” operation (or count if you want to see how many duplicates).
4. Filter the grouped table to keep only NNumbers with a count > 1.
5. Expand to show the original StaffID and NNumber columns.

Step-by-step:

• Home > Transform Data (opens Power Query)
• Select NNumber column > Home tab > Group By
• Group by NNumber, use “Count Rows” as the new column
• Filter the Count column for values greater than 1
• Expand the new column to get your StaffID/NNumber pairs

This will give you exactly the output you need—no DAX required!
translation and formatting supported by AI

[johnt75]

Create a measure like

Num entries =
CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[NI Number] ) )

Add this to a table or matrix visual with both the staff ID and NI Number columns and filter the results to only show when the value is greater than 1.