Still duplicates after removing duplicates

[terrells]

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?

[Alaria]

Just ran into this problem and I found that tranforming the field to UPPERCASE, then TRIM, CLEAN, (right click the column name, all are under Transform) and then removing duplicates works.  For whatever reason PBI recognizes ABC, abc, Abc, AbC, &c each distinct values (even blanks before and/or after create new distinct values) when removing duplicates however changing everything to uppercase, then trim, clean, and THEN removing duplicates seems to bypass any of the inconsistent rules that Microsoft hasn't bothered to fix or at least be clear about. 

[AndrewUrban]

This issue was taking years off my life, thank you so much!

[Anonymous]

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

[terrells]

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! 

[v-qiuyu-msft]

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"). 

Best Regards,
Qiuyun Yu

[cam0082]

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

[Anonymous]

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

[Anonymous]

This worked for me. Thank you so much

[v-qiuyu-msft]

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

[terrells]

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.

[v-qiuyu-msft]

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

[terrells]

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