Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Data outside the range parameter of incremental refresh changes

I pull my data with Odata feed, after i create parameters for the incremental refresh, Then i refresh the table -> Its seem that data outside the range changes also, is that mean the Incremental refr...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks selimovd, I will explane myself again:
Even if i put:
Store 5 years
refresh 2 weeks
data that older then the last 2 weeks will also update...
Infact, all of our Data is updated....

selimovd's avatar
selimovd
Icon for Most Valuable Professional rankMost Valuable Professional
5 years ago

Hey Anonymous ,

 

sorry, thought that was a question about the time window 😉

 

Can you check in your query if the steps where you filter by date are still in query folding? Can you make sure that the column you filter has the date format and that the parameters are called RangeStart and RangeEnd?

 

See the details about incremental refresh here:

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#supported-data-sources

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    selimovd The data that come from the Odata feed is in Date/time/timezone format so i create new column with the function DateTimeZone.RemoveZone([oldDate]), then i change the column type to Date/Time and filter thos column with RangeStart & RangeEnd, when i look in the 'Diagnose step' on the new column - the HTTP request does not use $filter, But the filter on the new column is working fine.

    In every step the 'View Native Query' is disabled, but i still get the 'Unable to confirm if the M query can be folded...'

    • selimovd's avatar
      selimovd
      Icon for Most Valuable Professional rankMost Valuable Professional
      5 years ago

      Hey Anonymous ,

       

      then that seems to be the reason. The "View Native Query" has to be possible for the step where you filter by date. The reason is that the engine is using the parameter and sending it directly to the source (... WHERE [date] > RangeStart and [date]< RangeEnd). Like this only the required data will be selected.

      If Query Folding at that step is not possible, it means Power Query would have to load all existing data into Power BI and then delete again after the transformation. And that's not the idea behind Incremental Refresh.

       

      In your case try if it works if the filter for RangeStart/-End ist the first step in your query. Otherwise you need a date column in your source. 

       

      If you need any help please let me know.
      If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
       
      Best regards
      Denis
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        selimovd Thanks again for the replays, the 'View Native Query' disabled from the start in my querys, and for example, if i have data from 1/1/2020 to 1/12/2020,
        and i make new column of date, and create the parametrs as:
        RangeStart - 1/6/2020
        RangeEnd - 1/12/2020
        It will fetch the correct dates, but only those dates, when i click 'Close&Apply', and publish the query - All the old data is gone.

         

        I saw that Microsoft made guide to create new column like i did, so who can i make it work?

        If the 'View Native Query' is disabled from the start, maybe its something else that i didnt understand in the process ?