Showing results for 
Search instead for 
Did you mean: 
New Member

Incremental refresh issues

I'm trying to implement Incremental Refresh on a large dataset.


We have the dataset pre processed elsewhere because it's simply massive and we're only looking to use the most current record of each item.


My issue is that i have incremental refresh set to store 3 years of data and update data in the past year only.


If the most current record in our system was between 1 and 3 years old, then it's in the historical dataset and not the one being refreshed.


If this old record is newly modified, it will a current date and fall into my incremental refresh policy criteria, but when the refresh data is paired with the historical data, i now have two records of a single item and it invalidates my one-to-many table relationship and the data refresh fails.


I've tried implementing table.buffer(), but because you need to load the table into memory before you can sort the column by date and then remove duplicates, it results in the incremental refresh failing due to memory.


Any recommendations or solutions to this issue?

Frequent Visitor

It sounds like your issue might be in the date field that you are using for the incremental refresh.


If you use a date field which can change on a given record, then on successive data refreshes after that field has changed the record could appear in different incremental refresh partitions. Partitions are only cleared and reloaded ('refreshed') if they are within the refresh window. So if your record had made it into the historic partition then it would remain there, as well as appearing in the refreshed partitions - as you described.

The fix would be to use a date field from the table which does not change.


You could then consider using your 'last modified date' field as the Detect Data Changes field to further minimse your data refreshes.

Helpful resources

PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors