Incremental Refresh misunderstanding ?

[FXFelix]

Hi,

Please apologize for my newbie question, but I think there is something I misunderstand in the functionality "Incremental refresh".

I thought the aim of incremental refresh was to store for example 5 years of data, and to refresh only the last 3 months. But if I am right, the date/hour filter is applied in Power Query, so only the last 3 months are present in the filtered table.

So how is it possible to show the data of the last 5 years before the 3 last months ?

Thanks in advance for your answer...

[FXFelix]

Thanks for your answer, but unfortunately I still not understand.

If you "get everything ready for incremental refresh", then the data is filtered between RangeStart and RangeEnd in PowerQuery, and obviously the data before RangeStart and after RangeEnd are deleted from the dataset...

I think I'm missing a step, but I don't know which 🤔

Maybe you have to set to RangeStart a value inferior to the oldest value in your dataset, and to RangeEnd a future value, for example 31/12/2050 ?

[collinq]

Hey @FXFelix ,

What I was suggesting is that you run the refresh without incremental.  That way you have everything you need.  And then, after you have all the details in the dataset you can set up the incremental refresh and that will refresh only the data for the range that you are looking to incrementally add/refresh and the old data will still be there.  Basically - get everything that you want/need the first time.  Then, use incremental refresh to add to it over time.

[FXFelix]

Thanks for your answer and your time @collinq 🙂

You're right, I can do this, and it will surely work. I will have the full period of data I want, but as soon as soon I will set up the incremental refresh, it won't be the case...

You see what I mean ?

[collinq]

Hi @FXFelix ,

I do understand what you are saying but I would respectfully submit that you have not configured something quite the way it is designed.  The point of incremental refresh is to ADD (or modify perhaps) rows and not to OVERWRITE with only the latest stuff.  I know that you have seen the Microsoft article on this but perhaps this entry by Chris Webb would be helpful for you:  Chris Webb's BI Blog: Keep The Existing Data In Your Power BI Dataset And Add New Data To It Using I...

[FXFelix]

Thanks @collinq for your help, I will try again 🙂

[FXFelix]

Hi,

I am sorry to exhume such an old thread, but the misunderstanding is still present in my mind 🙂

I have tried to make a simple report today to understand but in vain...

For example, I have a report with three years of data, and I have a visual in the report showing those three years.
If I set up incremental refresh for the last month, how can I still show the three years in the report ? Since data is filtered to the last month in Power Query...

Hope someone can help me... !

FX

[v-frfei-msft]

Hi @FXFelix ,

Incremental refresh enables very large datasets in the Power BI Premium service with the following benefits:

• Refreshes are faster - Only data that has changed needs to be refreshed. For example, refresh only the last five days of a ten-year dataset.

• Refreshes are more reliable - It's no longer necessary to maintain long-running connections to volatile source systems.

• Resource consumption is reduced - Less data to refresh reduces overall consumption of memory and other resources.

For more details, please check the online document.

https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh

[FXFelix]

Hi and thanks for your answer.

I had read that link before my post, but it did not enlighten me 🙂

Let's explain what is hard for me to understand...

In one of my report, I want to show data from 2017 to now, and I want to refresh only the last 3 months.

So I have set up incremental refresh in this report, RangeStart value to the first of april and RangeEnd to the first of july, for example. But applying those parameters in the Power Query editor delete data before RangeStart in the report.

So how can I show the data before the RangeStart if this data is absent from the report ?

[collinq]

Hey @FXFelix ,

In this sections of that link (https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh#refresh-ranges) the article explains how to keep the older data.  Also, if this isn't working the way you expect, you can get everything ready for incremental refresh and then run the report so that all the data you expect comes in and THEN turn on incremental refresh and from then on it will keep adding the data you need.