Forum Discussion
Prevent Duplicate error when trigger PBI incremental refresh on continuously changed Azure Cosmos DB
- I am setting the incremental PBI refresh on Azure CosmosDB that the data is continuosly changed/ updated. Today PBI refresh shows an error like ""Data source error: Column '<oii>case_id</oii>' in Table '<oii>bot_case</oii>' contains a duplicate value '7be939c9-e38a-ef11-ac21-002248049b9c' 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.""
- the case_id in the DB should be unique. after querying the CosmosDB, I only see 1 record. however this case_id was updated by another team during the time PBI refresh.
- what should I do to prevent this situation?
2 Replies
Hi Anonymous ,
To prevent this from happening in future, write your query in such a way that extracts only one case id.
For example- Write a group by query on case id, and other keys. This will ensure only no duplicate case id entry.
Thanks,
Pallavi
Hi, Anonymous
I am glad to help you.
As pallavi_r said, you need to make sure that the case_id is always unique.
You can also use a grouping query to group by case_id and select the newest record based on a timestamp or other unique field. This ensures that only one row per case_id is processed during the refresh process.
You can also review and adjust the incremental refresh policy in Power BI to ensure that the policy correctly handles changes and updates to data, especially fields used for keys.
Of course, you can also consider partitioning the data in Cosmos DB to minimize the possibility of conflicts during updates. This helps to manage and isolate updates more efficiently.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
