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 0...
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

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