How to initiate an incremental refresh in blocks of 6 months

[GeekAlfPro]

Hi,

i've data to upload in a report.

I want to import data, but i've a large amount of rows.

My source doesn't allow me to import more than 6 months, but i need to import about 3 years of data.

How can i set up the incremental refresh ?

Thanks

[Anonymous · Accepted solution]

Hi,@GeekAlfPro ,I am glad to help you.

According to your description. You want to  import the 3 years of  data to the report but your data source doesn't allow you to import more than 6 months. Now you want to break out of this limitation by setting an incremental refresh.

If I understand you correctly, you can see my advice below.

What type of data is your data source, is it a common database, such as MySQL, if you can provide some information about the data (for example, the field of the table you set up the incremental refresh is: whether there is a Date/Time type field, or the type of your data source) this information will help to solve your problem.

Requirements for incremental refresh:

1.The data source needs to support query folding.
2.Configure the incremental refresh data to have a Date/Time field.

If you want to configure a valid incremental refresh, you need to check whether your data meets the above conditions.

If your intention is to break the limitation that a single query of the data source can only query a time range of 6 months, and show 3 years of data in the report, you can try to set 3 years of data in the report.

You can try to split the 3 years of data into 6 queries, and append the data from the final 6 tables to a single table (in power query).

Then set up an incremental refresh on the total table with the 6 queries merged into it

(because the first query after the incremental refresh is configured is a full refresh: you need to perform a unified query on all the data displayed in the report, that is, for the first refresh, the query is for a range of 3 years of data, and only subsequent refreshes will be performed according to the configuration of the incremental refresh)

like this:

If you configure it this way, you will be able to show the data in the report for the last three years.

You can also try to use custom functions, by configuring Using custom functions, you can reduce some manual operations.
Here is the URL about how to use custom functions:
URL:Using custom functions in Power Query - Power Query | Microsoft Learn

If you have successfully merged three years of data, displayed it in the report, and ensured that the data source supports query collapsing and has fields with date/time data, then you are ready to configure an incremental refresh.
like this

You need to change the refresh policy to a history partition of 3 years (report on service retains up to the last three years of data) and a refresh partition of 1 month (after the first full refresh is performed, subsequent incremental refresh operations will only query and update the most recent month's data)

After publishing the report to Power BI Service and configuring the gateway connection, you can do the incremental refresh operation normally.

Here are some official documents about incremental refresh, hope you can help.
Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GeekAlfPro]

For Example, can i initiate the first refresh, like that : 

then publish the report in pbi services and set up the report like that

and republish an so on ?

Thanks