Incremental Refresh duplicates existing data instead of replacing it with updated variant

[T_W12]

Hi,

New to power bi and I've set up some charts for a client and due to their growing influx of data, am trying to implement an incremental refresh so that we only add new data or replace existing data that may have been updated the day before. However, I keep encountering a problem where the incremental refresh adds the updated data on top of the existing data instead of replacing it, essentially duplicating it. 

I've looked on other forums for similar problems and workarounds but there doesn't seem to be much about it. I understand query folding is neccessary when implementing an incremental refresh however it always is disabled (i.e. greyed out) when I come to enabling it. There was a number of fields types I needed to manually change when originally importing the data as we encountered issues where dates and times were being stored in US format instead of UK when we published it, so I'm not sure if that could be the reason for query folding being disabled.

Anyway, I'm at a bit of a loss as to why the incremental refresh seems to be duplicating the data rather than just replacing the existing data with the updated variant. Would greatly appreciate any advice from someone who may have experienced a similar issue or knows of a workaround.

Thanks for any help.

[lbendlin]

"I understand query folding is neccessary when implementing an incremental refresh "

No, that is not accurate. While highly recommended, query folding is not a strict requirement for incremental refresh.

Your problem is different. What you actually want is differential refresh, not incremental refresh. Incremental refresh expects data to be immutable. Your data changes after the fact. To mitigate that you need to come up with a slightly different strategy, extending the lookback period in a way that you catch the majority of data changes. Another approach is to have transaction IDs in your data that identify the change cadence for your data rows, and allows you to pick only the latest version of each transaction.

Hopefully one day Power BI will support differential refresh.