Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
cvegter
New Member

Removing rows with unique values

Hi, 

 

I have a data set (~30 000 rows) where instead of removing the rows with duplicate values, I would like to remove the rows that have a value that's unique. There are too many rows to toggle off manually all the values that are only mentioned once. I can't seem to work out how to do this but surely there must be a way. This is because we need a list with solely the duplicates.

 

Edit: came here with this question because using the keep duplicate rows in the query editor also keeps the singular values. 

 

Thanks! 

1 ACCEPTED SOLUTION
p45cal
Super User
Super User

There'll probably be many ways but there are 2 in the attached Excel workbook below.

In the Power Query editor after loading the data:

Version 1

1. Add an index column

2. Group the rows on the fields you want to include as duplicates (not the index column!) creating All Rows and Count

3. Filter out the 1s form the Count column

4. Expand the Tables column but only the index column

5. Optional: Sort on the Index column to retain original source data row order

6. Remove the Count and Index columns.

 

Version 2

1. Group the rows on the fields you want to include as duplicates creating a Count column

2. Filter the Count column to keep the 1s

3. Remove the Count column (leaves you a table with only rows that occur once)

4. Merge the Source table with the table in step 3

5. Expand this new column choosing any one of the fields (the field chosen must have no nulls)

6. Filter this expanded column for only nulls

7. Remove this expanded column.

View solution in original post

2 REPLIES 2
v-karpurapud
Community Support
Community Support

Hi @cvegter 

Could you please confirm if your query have been resolved the solution provided by @p45cal ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

p45cal
Super User
Super User

There'll probably be many ways but there are 2 in the attached Excel workbook below.

In the Power Query editor after loading the data:

Version 1

1. Add an index column

2. Group the rows on the fields you want to include as duplicates (not the index column!) creating All Rows and Count

3. Filter out the 1s form the Count column

4. Expand the Tables column but only the index column

5. Optional: Sort on the Index column to retain original source data row order

6. Remove the Count and Index columns.

 

Version 2

1. Group the rows on the fields you want to include as duplicates creating a Count column

2. Filter the Count column to keep the 1s

3. Remove the Count column (leaves you a table with only rows that occur once)

4. Merge the Source table with the table in step 3

5. Expand this new column choosing any one of the fields (the field chosen must have no nulls)

6. Filter this expanded column for only nulls

7. Remove this expanded column.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.