Incremental Refresh Unexpectedly Truncating Data

Hi all,

I have a fairly large dataset, +60m rows of data going back 5 years, which I intend to utilise incremental refresh on to be able to efficiently build and populate reports. The technology used is an ODBC connection on a Power BI Pro license, therefore I'm restricted in what I can do backend with partitions and the web services 2 hour time limit.

The incremental refresh settings I have are:

• Archieve Data going back 7 years
• Incremental Refresh data going back 7 days
• Unticked "Only Refresh Complete Days"
• Unticked "Detect Data Changes"

My main challenge is being able to load the historical data into the data model. The maximum time window that I can get squeezed into a 2 hour limit is one years worth of data. So, bareing in mind that I have a further 4 years of data that I want to populate, I am attempting to get creative by introducing the following logic:

1. Create a view pointing to my main data table
2. Add a calculated field to the view (eg: load_date_time) which is the RangeStart / RangeEnd filtered column for Incremental Refresh
3. Set the view up so that data within my table that has a date 2018 has a load_date_time of "2018-01-01"
4. Publish report to web service and refresh (this step is successful - all data for 2018 enters the report)
5. Alter view so that data within my table that has a date of 2019 has a load_date_time of today minus seven days
6. Refresh data to populate 2019 data (again, this step is successful. I now have 2018 & 2019 data populated)
7. Wait one day so that the 2019 data loaded with a refresh date of today minus 7 days falls outside of the "incremental refresh" policy of 7 days
8. Alter view so that data within my table that has a date of 2020 has a load_date_time of today minus seven days and refresh
9. Repeat steps 5 - 8 until data is fully populated and then point my load_date_time to the genuine date so that incremental refresh continues to work as intended

My issue with the above is that when I get to step 8, the data refreshes and populates but at the same time it truncates all the data that was loaded in step 7, even though the "load_date_time" is now day 8 and therefore outside of the 7 days refresh policy.

I attempted to wait two days between refreshes, yet I experienced the same behaviour. ie - step 7 data is truncated.

So, recognising that the above is a bit of a hack of the system in how it was designed to work, my two questions are:

1. Why does my above hack not work? What am I missing?
2. Is there a better alternative way to get data populated into my report without breaching the two hour time limit?

All assistance appreciated.