Forum Discussion
Microsoft EmployeePower BI Incremental refresh not removing duplicate records
Hi AnnOminous ,
Thank you lbendlin for your inputs, You're absolutely right Power BI Incremental Refresh is designed for append only scenarios based on an immutable datetime column, such as CreatedDate. It's not intended to handle differential updates or Slowly Changing Dimensions Type 2 (SCD2), where rows may change over time and retain history using LastModifiedDate.
In this case, since duplicate user_ids are being introduced due to updated records appearing in new partitions, the only reliable solution is to reprocess all partitions that may be affected by those changes. This ensures that deduplication logic can be applied across the entire dataset, not just within each partition.
If full reprocessing isn't feasible or efficient, a common workaround is to:
Load all user records including duplicatesinto your Users table, Then use a DAX calculated table or virtual relationship to filter for only the latest record per user_id based on a ModifiedDate or EffectiveDate field. This way, you maintain the integrity of incremental refresh while ensuring only the latest version of each user is used in your model relationships and visuals.
Hope this helps.
Best Regards,
Chaithra E.
Community SupportHi AnnOminous ,
Thank you lbendlin for your inputs, You're absolutely right Power BI Incremental Refresh is designed for append only scenarios based on an immutable datetime column, such as CreatedDate. It's not intended to handle differential updates or Slowly Changing Dimensions Type 2 (SCD2), where rows may change over time and retain history using LastModifiedDate.
In this case, since duplicate user_ids are being introduced due to updated records appearing in new partitions, the only reliable solution is to reprocess all partitions that may be affected by those changes. This ensures that deduplication logic can be applied across the entire dataset, not just within each partition.
If full reprocessing isn't feasible or efficient, a common workaround is to:
Load all user records including duplicatesinto your Users table, Then use a DAX calculated table or virtual relationship to filter for only the latest record per user_id based on a ModifiedDate or EffectiveDate field. This way, you maintain the integrity of incremental refresh while ensuring only the latest version of each user is used in your model relationships and visuals.
Hope this helps.
Best Regards,
Chaithra E.
Microsoft Employeev-echaithra, thank you for the additional information. When you say that the only reliable solution is to reprocess every partition, that's effectively saying that I need to do a full refresh of that table, and apply the deduplication step to the refreshed data, correct? Or is it something I could manage in, say, SSMS for example?