Forum Discussion

83dons's avatar
83dons
Helper III
1 year ago
Solved

Identifying duplicates in one column

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.

 

Need Help
  • burakkaragoz's avatar
    burakkaragoz
    1 year ago

    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

5 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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.

  • burakkaragoz's avatar
    burakkaragoz
    Super User
    1 year ago

    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's avatar
      83dons
      Helper III
      1 year ago

      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's avatar
        burakkaragoz
        Super User
        1 year ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.