Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
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.
Proud to be a 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.
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.
Proud to be a Super User! | |
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
66 | |
34 | |
25 | |
22 |
User | Count |
---|---|
97 | |
96 | |
58 | |
45 | |
42 |