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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jondall42
Frequent Visitor

I can't remove duplicates.

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.

1 ACCEPTED SOLUTION

Yes.  I used trim and it worked,

View solution in original post

10 REPLIES 10
v-kelly-msft
Community Support
Community Support

Hi  @jondall42 ,

 

Is your issue solved now?

 

Best Regards,
Kelly

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

Yes.  I used trim and it worked,

Hi  @jondall42 ,

 

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
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

Hi  @jondall42 ,

 

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

vkellymsft_0-1626941967615.png

 

 

Best Regards,
Kelly

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

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

Hi @jondall42 ,

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors