Data Duplication Issue in Daily Power BI Desktop Refresh

[eduardobujak]

Good morning, everyone!

I'm experiencing a problem with my Power BI Desktop file when it performs its daily refresh after being published to the workspace.

My data source is an incremental dataflow that always keeps the most recent record. However, when I refresh my PBIX file, the data is duplicating, retaining both the old and new records. This is causing a 1:1 relationship error between tables.

I have reviewed the dataflow to ensure it is correctly removing duplicates and keeping only the most recent record. Despite this, the issue persists when I refresh in Power BI Desktop.

Attached are images of the problem for better illustration.

Has anyone encountered this issue before or have any suggestions on how to resolve it?

Thank you!

[Attachment: Images of the issue]

Dataflow:
Dataflow

Refresh Error:
Refresh Error

Image after removing relationships to illustrate the problem

Image after removing relationships to illustrate the problem

[Anonymous]

Hi @eduardobujak 

Thanks for the reply from @lbendlin .

@eduardobujak , I found a document with a similar problem to yours, and the solution there is to change the table relationship to many-to-one. I hope it helps you.

Solved: Refresh error: "...contains duplicate value...and ... - Microsoft Fabric Community

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

My data source is an incremental dataflow that always keeps the most recent record. 

That's not how incremental refresh works. It is called "incremental" for a reason, and not "differential". Incremental refresh expects your data to be immutable.  If your data changes then you need to expand the refresh window, and worst case will have to abandon incremental refresh altogether.

[eduardobujak]

The problem is not with the dataflow structure, as it is keeping only the most up-to-date record. The issue is more like a cache problem in the .pbix file, because, as illustrated above, there is no longer a row without a value in the deletedat field in the dataflow. It seems like the pbix file does not remove the data when starting the daily refresh, so the records that were updated conflict with the old information.

[lbendlin]

Show the entire power query code. Which field is the basis for the range filters?

[eduardobujak]

Here is the complete Power Query code. The base field for the filter is updatedat.

[lbendlin]

Updatedat is not  a suitable field. Incremental Refresh needs something immutable like Created Date. Even then you have to refresh historical partitions if the row contents changes.