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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
KatrinL
Regular Visitor

How to automatically remove duplicates in PowerBi + Power Query / How to Avoid duplicate errors

I would appreciate your help removing duplicates / avoiding duplicates in data pulled automatically out of Salesforce.

 

I have 4 tables:

1. A table listing closed service tickets with ticket number and unique ID, each ticket can only be closed once.

2. A table listing service tickets with ticket number and unique ID that have been reviewed by admins, each can only be reviewed once.

3. A table listing the company's departments

4. A table listing billing types.

 

Columns most frequenty affected, these exist in both tables 1 and 2:

- 'WO Link'- column created automatically in Power Query addign a prefix to the unique ID, making the ticket number a hyperlink in my table

- 'Work Order: Work Order Number' - list of tickets numbers in both tables

 

Tables 1 and 2 each  have a many to one relationship with tables 3 and 4. This relationship is not based on any of the above columns.

 

My power Bi report keep going into error, the erros are always similar: "contains a duplicate value 'xyz.com/a1K5d00000ASUZx' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table. Table: OPKPI - PL - Closed WOs."

 

Cause of duplicates:

- Someone going against procudure and re-opening tickets

- The ticket unique ID assigned by Salesforce is sometimes not unique enough. Example: anTeX and ANtex. Only the case is different and Power Bi reads this as a duplicate

 

I have been unable to find a way to automatically exclude duplicates in the ticket number column and unique ID columns.

At present I manually go into Salesforce and exlude specific duplicate ticket numbers or unique IDs from the report. There has to be a better solution than this.

 

Thanks in advance!

 

 

1 ACCEPTED SOLUTION
audreygerred
Super User
Super User

Hi! Open Power Query, right click on teh column you want to remove duplicates on, select Remove duplicates. This will add a step to Applied Steps and the de-duplication will happen each time you refresh.

audreygerred_0-1699979470786.png

 





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

Proud to be a Super User!





View solution in original post

3 REPLIES 3
audreygerred
Super User
Super User

Hi! Open Power Query, right click on teh column you want to remove duplicates on, select Remove duplicates. This will add a step to Applied Steps and the de-duplication will happen each time you refresh.

audreygerred_0-1699979470786.png

 





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

Proud to be a Super User!





Hi Audrey, thanks for your reply! The data refreshes automatically mutliple times each day and also when I am out of the office. I hope very much to find a way to remove the duplicates automatically. Manually removing them that way works though - I've just tried it - thanks.

Hi! Doing it this way isn't manual. It is now part of your applied steps in Power Query so each time the data refreshes it will complete that step and do the deduplication. You set it up the one time and that's it. 





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

Proud to be a Super User!





Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.