Forum Discussion
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?
12 Replies
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.
This issue was taking years off my life, thank you so much!
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-msftCommunity Support
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
- 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-72779688874d
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 YuHi
"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
