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

[KatrinL]

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!

[audreygerred · Accepted 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.

[KatrinL]

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.

[audreygerred]

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.