Data outside the range parameter of incremental refresh changes

[Anonymous]

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 refresh does not work?
The 'View native query' is disabled but when i check the 'Diagnose step' i saw Http request that shows that query folding is working..

Is that mean that the incremental refresh does not work or its someting else that i didnt understand?

[parry2k]

@Anonymous for IR to work, the source should support query folding. Here is a blog post that talks about where query folding is not possible and but still how you can use IR. 

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[selimovd]

Hey @Anonymous ,

when you set up an incremental refresh, you define a time window that should be refreshed and a time frame that should be kept. For example, keep the data of the last 5 years and refresh the last 2 weeks.

For every refresh only the last 2 weeks will be refreshed. If you have changes 3 weeks ago, they will not appear in your data set as they are out of the refresh window. In this case you have to expand the window or define that changes after a certain amount will not be considered.

The refresh window is a rolling window by the way. So every day the window moves by one day. In 2 weeks the data until today will be refreshed. But I guess that was clear.

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

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]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@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]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@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 ?

[selimovd]

Hey @Anonymous ,

for Incremental Refresh Power Query has to be able to "generate a single query expression to retrieve and transform source data".

When your data source is not available in a way that Power Query can send a single query, then I'm afraid Incremental Refresh is not possible.

Do you have the possibility to change the data source in order to make it work with a single query?

Best regards

Denis

[Anonymous]

@selimovd 
I cant change my data source, but in the data source i have date column with format - Date/Time/timeZone, Is there some process that i can do with that column?

Because when i filter with this column - in the 'Diagnose step' i see that the HTTP request is with $filter, unlike the HTTP with the column that i create manually.

[selimovd]

Hey @Anonymous ,

you need a column in the data source with the format Date. The format Date/Time/timeZone or Date/Time won't help you.

If you can change your source, then change the column or add a column with the Date format and try the approach with that column.

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic