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]

sorry previousDate is an extra code that I should have commented out. my bad I apologize. This is what I have now

[lbendlin]

Replace line 18 with RangeStart and line 19 with RangeEnd.  Drop the CurrentDate calculation, it is not required.

[stribor45]

But assigning previous and current date for those values will call API endpoint and get data from April 1 to

today.  How would I get data from Jan - March?

[lbendlin]

The Power BI service will do that automatically during the incremental refresh, supplying the appropriate dates for each partition.

[stribor45]

I am not disputing that fact. all I am saying that i only have lines between rows 37-50. My first 36 lines of code is juust code related to API call that gets the data. 

[lbendlin]

That's exactly the point.  If you make the parameters part of your API call filters then you can implement your own query folding.

[lbendlin]

Yes. I am telling you that is way too late.

[lbendlin]

partitions are filled on the second refresh.

[stribor45]

@lbendlin do i do that from the desktop in my model? i tried that and it didnt work for me

[lbendlin]

no, if you do that in the desktop it will reset back.  You have to refresh in the service to distribute the data across the defined partitions.

[stribor45]

How do i go back on track now?

[lbendlin]

Have you done a service refresh after your last publish?  has it completed?  Use SSMS or DAX Studio to confirm that you indeed have more than one partition, and that they are filled with rows and have different timestamps.

[stribor45]

This is what i have done again

1. Refresh in Desktop
2. Refresh in Service which completed fine
3. Looked at the report and seeing only April data
4. Used external tool to conect to Dax Studio and see only one table with April data only

[lbendlin]

Show your Power Query

[stribor45]

you mean the code?

[lbendlin]

Show how you applied the RangeStart and RangeEnd filters.

[stribor45]

[lbendlin]

They can't both be inclusive. One has to be inclusive and the other has to be exclusive.

[stribor45]

what do you mean?

[lbendlin]

Your filter settings will cause data to bleed over across partitions.  Needs to be like this:

RangeStart <= DateTime column < RangeEnd

[stribor45]

This is what I have which is saying filter where "requesteddate is bigger or equal to April 1 and requesteddate is smaller or equal to April 9. Sorry, can you maybe try to explain it differently because I do not understand.