cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
terrells
Regular Visitor

Still duplicates after removing duplicates

After removing duplicates there are still some duplicates remaining. The Table summary at the bottom of the table knows that there are duplicates as it says TABLE: Dimension_PF (63,036 rows) COLUMN: Public Folder Path (63,034 distinct values), yet if I select Remove Duplicates the duplicates remain.

 

Any ideas?

10 REPLIES 10
Anonymous
Not applicable

Check to see if you have blank or null values in the column.  Those can throw off the remove duplicate step.

Yes I've experienced that before which is something I always check for but on this occasion with this data there are no blanks or null values. As previously mentioned even after the remove duplicate step, the counts at the bottom or the table when selecting the column still recognises that there are duplicates i.e. TABLE:.... (63,036 rows) COLUMN (63,034 distinct values)

 

Confusing to say the least.

 

I'll locate them manually and just remove I think! 

Hi @terrells,

 

Please check if the column "Public Folder Path" contains uppercase or lowercase for the same character, for example, the column has two values "A" and "a", it will be treat as distinct value. While after loading to data model, those two characters will be treat as the same character (based on my test, it's "A"). 

 

q5.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello! Had the same issue.

 

What you need to do is add a Trim step before the Remove Duplicates in the Query Editor. It happens you can have "XYZ " (with blank spaces at the end) and "XYZ". PowerBi will count the as independent values. Trim function will eliminate those blank spaces at the end.

 

Best regards,

Cris

Thank you @cam0082. I was struggling with this issue for a long time. Never occurred to me that this might be the case

Anonymous
Not applicable

This worked for me. Thank you so much

v-qiuyu-msft
Community Support
Community Support

Hi @terrells,

 

From your description, it seems that you are trying to remove duplicate columns in Query Editor, right? 

 

When you click the "Remove Duplicates", which columns do you select. If you only select column "Public Folder Path" then click the "Remove Duplicates", it will remove duplicates in column "Public Folder Path". While if you select column "Public Folder Path" and other columns then click "Remove Duplicates", it will delete duplicate rows when this column "Public Folder Path" and other selected columns have the same values. For more information, see: https://support.office.com/en-us/article/Remove-duplicates-Power-Query-d9cffc69-dc5d-4d94-8b66-72779...

 

In your scenario, I would suggest you go to Query Editor, select column "Public Folder Path" then click "Remove Duplicates" then apply the change to see if the issue is gone. 

 

By the way, please run the latest Power BI desktop. 

 

Best Regards,
Qiuyun Yu

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi

 

"In your scenario, I would suggest you go to Query Editor, select column "Public Folder Path" then click "Remove Duplicates" then apply the change to see if the issue is gone. "

 

This is exactly what I have done before posting the issue, and therefore the reason why I posted the issue i.e. removing the duplicates by the above method does not remove all the duplicates, and as mentioned in the opening post, power bi seems to be aware that there are still duplicates despite 'removing' them via query editor, as the counts at the bottom of the page when you select the public folder column displays the rows and distinct values.

 

But thanks anyway.

Hi @terrells,

 

Please go to Query Editor, check if Remove Duplicates for the column "Public Folder Path" is the last step. 

 

Does the issue happens to this one specific report or all reports? 

 

Which Power BI desktop version do you run? Could you try the latest one. 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi

 

Yes it is the last step, not tried it on any other report so far but not had any problems in the past.

 

Current version is Nov 2017 but I will update to Dec 2017 version as soon as available for download and retry.

 

Thanks

Stuart

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors