Auto Append and Delete Duplicate Data

[Colinu]

HI All If you can assist with this be very helpful.

I have data in a few tables with the common link in a column named 'Submmision ID'. In this column there are duplicates and this caused issue on creating the needed relationships. to get around this I used the method of Appending the the column into a new table and then removing the duplicates. this left me with a table with Submission ID that had no duplicates that is could link all my other column through. 

Seen in the clip:

https://youtu.be/vAvQ8pCnWDk

The one issue i am left with is that when is on Refreshing the data to my SQL data source to collect the new data, new duplicates are added. i then need to manually go though the process in the clip all over again. 

Is it possible to automate the process above so that any new data that is duplicated is appended and then duplicates remove? 

[v-yuta-msft]

Hi Colinu,

In your senario, I would recommend you to use increment refresh: https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh.

Regards,

Jimmy Tao

[Anonymous]

Hi @Colinu

The applied steps in M , will be performed in each refresh and as per that new duplicates should be removed and only unique duplicates should be added to the new table and this should happen automatically . Request you to check your design step by step, may be with example data to undersatnd where its going wrong.

some more points :

* If any one of your table has master list which contains all values, then you dont need to do the append. directly take the distinct vales from that column adn use that as briding table.

* You can do this ( the above step) in more dynamic way in DAX. Create new table in Modeeling-> New table using DAX formula DISTINCT of that master duplicated column and use that in relationship.

Thanks

Raj

[Anonymous]

Hello, I have the same issue with duplicates when I add a new workbook to the folder source, I tried "remove duplicates" in the sample file after the append step, but it's not working, every time I add a new workbook to the folder the duplicates shows up again, I tried selecting columns but doesn't work neither, the only column with diffent values is the column with the source file name. I don't know what I doing wrong