I can't remove duplicates.

[Anonymous]

I cannot remove duplicates.  I tried changing to uppercase and then removing duplicates.  It says I have 12056 rows and 12054 distinct values.  I tried filtering null values through the python script I use to bring my data in.  I tried filtering out empty cells in power query.  I exported a table in BI to excel and it says there are 12054 rows.

[v-kelly-msft]

Hi  @Anonymous ,

Is your issue solved now?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous · Accepted solution]

Yes.  I used trim and it worked,

[v-kelly-msft]

Hi  @Anonymous ,

Glad to hear it,thanks for sharing your solution,could you pls make the reply as answered to let more people find the solution?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mahoneypat]

You've already done the uppercase thing, which gets a lot of people.  Do you have some values with hidden characters?  If so, try adding a trim step before your Remove Duplicates step.

Also, if not already, you can make a table visual with that column and add it again to get the count of rows, then sort by that to see which ones have >1 and are causing your issue.

Pat

Pat

[Anonymous]

However, I still cannot remove them.  Power Query does not work and it has too many rows and won't let me filter.

[mahoneypat]

The 1000 rows is just the preview.  The Remove Duplicates step works on the whole dataset when it loads.  Were you able to make a table visual that shows with values are duplicated?

Pat

[Anonymous]

I made the table.  However, I cannot delete the values.  I am currently just using a many to many relationship.  Since the two sets of duplicates are the same throughout the row, the many to many relationship functions the same as a one to many.

[v-kelly-msft]

Hi  @Anonymous ,

Have you changed the below setting to the entire data set in power query before removing duplicates?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

I found the duplicates.  I think deleting duplicates isn't working because it only deletes for the top 1000 rows in power query.

[mussaenda]

Hi @Anonymous ,

Removing duplicates does not only apply to top 1000 rows. it applies to your whole table. top 1000 rows is only for preview. Try checking other columns those might be the cause of data duplicate.

Hope this helps.