Dataflows Incremental Refresh

[ArvindJha]

Hello Team,

I need to implement incremental refresh the scenario is like i have 10 differnt countries in dataset and each country might have data till different date.

Say we have data for India till 1st Jan 2025

For Canada till 10th Jan 2025

For Australia 1st Feb 2025

So i need to do incremental refresh for India and look data beyond 1st Jan 2025 , for Canada beyond 10th Jan 2025 and Australia beyond 1st Feb 2025.

Thanks

[v-ssriganesh]

Hi @ArvindJha,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @ArvindJha,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @ArvindJha,
Thank you for reaching out to the Microsoft fabric community forum.

Thanks to @tharunkumarRTK for providing a great explanation and approach to handling incremental refresh. as mentioned, if the difference in last available dates between countries remains constant, setting the incremental refresh to the earliest last available date will ensure all countries are refreshed correctly. The suggested policy of "Refresh Last 2 Months and Store Last 2 Years" is a good approach when working with consistent data patterns.

However, if the last available date varies dynamically for each country, the standard incremental refresh settings may not be sufficient.

Alternatively, to handle this scenario dynamically, you can modify the Power Query script as follows:

• identify the last available date for each country.
• Find the earliest of those maximum dates.
• Use this dynamically as the RangeStart boundary.
• Apply incremental refresh using the calculated RangeStart.
  
  

If this helps, then please Accept it as a solution and dropping a "Kudos" so other members can find it more easily.
Thank you.

[v-ssriganesh]

Hi @ArvindJha,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.

[tharunkumarRTK · Accepted solution]

@ArvindJha 

If the difference in last available dates between countries remains constant, then setting the incremental refresh to the earliest date (e.g., 1st Jan 2025 for India) will ensure that all countries get refreshed correctly. Taking the example you shared, you can set the incremental refresh policy as 

"Refresh Last 2 Months and store last 2 years"  

this will ensure that all countries data will get refreshed.

However, if the date difference changes dynamically, this approach won't work because some countries might not refresh correctly. To avoid this issue, you need to write the M code in such a way that,

1. it will identify the maximum date available for each country, example code

LastDatePerCountry = 
    Table.Group(Source, {"Country"}, {{"MaxDate", each List.Max([DateColumn]), type date}})

2. Take the minimum date of MaxDate

MinofAllMaxDates = List.Min(Table[MaxDate])

3. If MinofAllMaxDates is less than RangeStart parameter then consider MinofAllMaxDates else RangeStart

4. Then use the RangeStart and RangeEnd paramters to filter your actual table.

https://learn.microsoft.com/en-us/power-query/dataflows/incremental-refresh

https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-incremental-refresh

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!