Failed refresh due to duplicates in data table created by users updating data during the refresh

[annalisekerr]

Hi

I have an odd problem that I have noticed and wondered if anyone else has experienced something similar and would know how to fix it

A report using data from Azure DevOps fails to refresh ever so often due to duplicate WorkItem ID's.  In this instance, there would never be duplicated created in DevOps so after a bit of testing I realised that it only happend when someone made an update to the WorkItem in Azure DevOps (data source) whilst Power BI was running the refresh.

For example,

The report is set to refresh at 0800hrs and would usually take 30mins.  So someone making a change to a WorkItem at 0815hrs would somehow create a duplicate WorkItemID number int he data table.

Other than setting the report to refresh during the night when no one is using Azure DevOps, does anyone have any ideas how we can avoid this?

[v-luwang-msft]

Hi  @annalisekerr ,

In my opinion, try not to manipulate the data when refreshing. Just as in a sql(or oracle), if we are doing an update to a table's data, the table will lock up and the associated query process etc. will be inefficient, so it is better to do nothing else with the table when it is locked up.

Pls refer the below links and check if it is helpful:

https://community.powerbi.com/t5/Service/Duplicate-value-error-while-refresh-without-having-a-duplic... 

Best Regards

Lucien

[annalisekerr]

Hi @v-luwang-msft 

Thank you, yes my imediate thought was to make sure the refresh is scheduled for during the night to ensure no one is using the data source.  The report users are not so keen on that idea 🤔, hence why I wondered if anyone had an alternative solution.