Incremental Refresh

[stribor45]

I am trying to test incremental refresh and in my power query i have data from January 1 2024 until today.  I also  have set up  parameters (RangeStart and RangeEnd) to be between April 1 - April 9.  I also have set up custom filter to filter data between these two dates. After this i have done "close and apply" to load this into my model where I also have set up increment policy (see attached) and published it in service. After this I did refresh in my model in desktop but in service I only see data for April and dont see any data from Jan, Feb and March. Any sugestions on what I am doing wrong?

[stribor45]

I almost just about finished. I was wondering how to make this RangeEnd parameter to always be the current todays date

[lbendlin]

Why would that be necessary?  It will mess with the meta data and reset the incremental refresh

Set RangeEnd to a date in the future.

[stribor45]

because I always have to hard-code today's date (see image in the previous post). 

[lbendlin]

You don't need to do that. The parameter values in Power Query have nothing to do with the actual partition boundaries in Incremental Refresh.  Think of them as a way for you (the report developer) to only use the necessary subset of data for development work.  Again - these values will be ignored/overwritten by the Power BI service.

Use a future date and then leave it be.

[stribor45]

How does the report and service them what data to pull?

[lbendlin]

Based on the refresh rules you specified in Power BI.  The service will also automatically merge partitions as needed when date boundaries are crossed (like when a new year starts).

[stribor45]

Ok make sense now I think. I am curious about why when I put the filtering on some of my data is missing in power query.

[Anonymous]

Hi @stribor45 ,

Did @lbendlin  reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best Regards,
Adamk Kong

[stribor45]

my numbers in visual in Desktop and Service do not match.  I saw some posts where dates in Service are in UTC

[lbendlin]

That's correct. Incremental Refresh uses a regular UTC calendar.

[stribor45]

so my RangeStart and RangeEnd need to be adjusted? My API endpoint takes DateTime values using my offest. how do I adjust them?

[lbendlin]

There are Power Query functions to shift timezones, or you can use #duration.

[stribor45]

I am more concerned about how this work. In my Desktop I call APIs which accept datetime like this with my time zone being 4 hours behind. i have to call it this way because by numbers depends on it. 

DateTime.ToText(RangeStart,"yyyy-MM-ddTHH:mm:ss-04:00")

 As you said before when refresh happens on Service side which after initial set up they will and since it is UTC time how do i do that? I dont understand how at the same time I call API's with EST time (-4 hours) and in service I call API with UTC time?

[lbendlin]

In such scenarios I always use UTC everywhere.

[stribor45]

@lbendlin I was able to make this work.  The only question I have is how do I ensure or check that the data in the red circle is archived (not fetched via subsequent refresh)? Thank you for your help on this issue I was having. 

[stribor45]

 ok sorry about the delay on this. This is what I have now. (see the images).  Please let me know if this looks good to get data from January 1 to today and how many times i have to refresh to set up the archives

[stribor45]

I can't. because RangeStart and RangeEnd won't accept datetimes like these yyyy-MM-ddTHH:mm:ss-04:00 and API endpoint requires datetime in this format

[lbendlin]

DateTime.ToText(RangeStart,"yyyy-MM-ddTHH:mm:ssZ")

DateTime.ToText - PowerQuery M | Microsoft Learn

[stribor45]

I followed multiple tutorials online about this and I thought I did everything in order.  Can you look at this code (see image) and maybe suggest how to adjust it because your last post kind of confused me because I do have date parametars in my query as I am requesting data from jan 1 until today but only want last few dats to be live and eveything else to be archive so it doesnt need to be refetched on every refresh since that data never changes

[lbendlin]

Assign RangeEnd to currentdate and RangeStart to previousdate.

Not sure which tutorials you refer to, but this one here is pretty good.

Troubleshoot incremental refresh and real-time data in Power BI - Power BI | Microsoft Learn