Incremental refresh - changes in old data

[Anonymous]

Hello,


I plan to configure incremental refresh.

Suppose we have data from 2015-2020. In addition, we have 2 columns - extraction date (update date) and sale date. We want to refresh data that has been extracted (updated) in the last 30 days.

What happens if we make some changes to the data from 2018 (The update date will change to the current date)?
Will PowerBI delete old lines and replace them with new lines? Will the rows be duplicated and will I need to detect changes and manually remove the duplicate rows?


Thank you in advance,

Jagoda

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Based on my understand, if we set store rows for where "extraction date" is in the last 5 years and refresh rows  where "extraction date" is in the last 30 days, then the refresh start at 2020-03-14 06:00:00 AM, it will refresh the rows where "extraction date" is between 2020-02-13 06:00:00 AM and 2020-03-14 06:00:00 AM, and all the rows where "extraction date" is between 2015-03-14 06:00:00 AM and between 2020-02-13 06:00:00 AM will keep as the same, and then the rows where "extraction date" is earlier than the 2020-02-13 06:00:00 AM will be dropped. 

So In your scenario, if the data in 2018 changed to earlier than 2020-02-13 06:00:00 AM,those rows will not change and keep as old if the refresh range is only set as 30 days. If it change to 2020-03-12 08:00:00, Based on our test, it will keep the old row and add a new rows: 

Please refer to following document about more information of Incremental Refresh:

https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh#configure-incremental-refresh

https://docs.microsoft.com/en-us/power-bi/service-dataflows-incremental-refresh#incremental-refresh-implementation-details

Best regards,

[Anonymous]

is there a way to prevent duplication of records, and just update the "old" record with the"new" one? without having to refresh on the entire period of 5 years? 

[IMett]

Does anyone have a proposal how to solve this? We have exactly the same issue.

[GilbertQ]

Hi there

if you use the detect data changes and put it on your Extraction Date Column when the incremental refresh happens it will remove those old rows and insert the new rows based on the date.

[vickydev83]

Since the underlying technology is SSAS, can this be done in SSAS or AS models too?