Forum Discussion
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...
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:
- In Power Query, select the NNumber column.
- Go to the “Group By” option in the toolbar.
- Group by NNumber, and add an “All Rows” operation (or count if you want to see how many duplicates).
- Filter the grouped table to keep only NNumbers with a count > 1.
- 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
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.