Setting up Incremental Refresh for Weekly Archive

[joshua1990]

Hi experts!

I need some guidance for setting up an incremental refresh.

I have a table that stores the sales data each week for last week:

Extract Date	Sales Data	Value
01.07.2024	26.06.2024	5
01.07.2024	27.06.2024	7

The extract date shows the date for the archive.

Now I would like to update the last 2 weeks based on the column and to store the last 2 years.

How would I need to define RangeStart and RangeEnd for that column and what do I need so define in the incremental refresh settings in the end?

[lbendlin · Accepted solution]

I believe the documentation describes the process well. Here is the short version:

- you create RangeStart and RangeEnd parameters in Power Query, set them as datetime, and specify default values that give you enough data to develop with (these default values will be ignored during the actual refresh)

- you add a filter step in your Power Query with an appropriate datetime column (for example Order Date).  Make sure to set RangeStart to inclusive and RangeEnd to exclusive.    RangeStart <= [Order Date] and [Order Date]<RangeEnd

- then in Power BI you set the table to incremental refresh for 14 days and storage for 2 years.

[lbendlin]

Incremental Refresh does not have weekly partitions. Best you can do is refresh 14 days and keep 2 years.

[joshua1990]

That is fine. But how would you setup the parameters, filters and the settings in the end?

[SaiTejaTalasila · Accepted solution]

Hi @joshua1990 ,

You refer this- 

https://community.fabric.microsoft.com/t5/Power-Query/Incremental-Refresh-with-Year-Week-Column/td-p...

Thanks,

Sai Teja 

[lbendlin · Accepted solution]

I believe the documentation describes the process well. Here is the short version:

- you create RangeStart and RangeEnd parameters in Power Query, set them as datetime, and specify default values that give you enough data to develop with (these default values will be ignored during the actual refresh)

- you add a filter step in your Power Query with an appropriate datetime column (for example Order Date).  Make sure to set RangeStart to inclusive and RangeEnd to exclusive.    RangeStart <= [Order Date] and [Order Date]<RangeEnd

- then in Power BI you set the table to incremental refresh for 14 days and storage for 2 years.