Important Considerations for Setting up Incremental Refresh on Power BI Desktop

[Kumail]

This simple and brief article is about Incremental Refresh setup in Power BI. By the end of this article, you will know more about incremental refresh, its advantages and setup process.

What is incremental refresh?

Incremental refresh is a feature in Power BI premium which enables more reliable, more efficient resource consumption and fast refreshes on very large datasets. This feature is available with Power BI premium tenant only.

Use Case

When working with potentially billions of rows of data, in order to use Power BI desktop efficiently we need to filter upon import. This is where incremental refresh comes in handy.

Important requirements for Incremental Refresh to Work:

• The data source must support “query folding” which is supported by most data sources that support SQL databases.
• The filter logic (filter is pushed to the source system when queries are submitted for refresh) is included in the source queries.
• The filter only applies to date time column.
• In case the date is used as a surrogate key in data warehouse of the form yyyymmdd. The function can be called by the filter step (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)

How to set it up?

Process

1. Filter data set using power query editor. It requires setting up 2 parameters;
2. Connect to a data source with import or direct storage mode. For this demo, I connected to MS SQL database.
   • Press edit queries from “Home” menu in Power BI desktop to navigate to power query window.
   • In query editor, select “New Parameter” from home tab.
   • Name new parameter “RangeStart” (Case sensitive), select type “Date/Time”, suggested values “Any Value” and current value “12/29/2010 12:00:00 AM” (first date of your period).
   • Create another parameter “RangeEnd” (Case sensitive), select type “Date/Time”, suggested values “Any Value” and current value “12/30/2011 12:00:00 AM” (last date of your period)Manage Parameters
3. Select table that you want to incrementally refresh and select date column to apply “RangeStart” and “RangeEnd” to custom filters.

     Step 1: Filter date column and go to custom filters.Custom Filter

     Step 2: Apply parameters “StartRange” and “EndRange” in custom filters.Filter Rows

     Step 3: Verify “filtered rows” step added in applied steps pane.

 Applied StepsNow close the query editor window and apply any pending changes.

Refresh Policy

With refresh policy, organizations have more control on their large dataset particularly how often it incrementally refreshed in Power BI service.

Refresh policy for incremental refresh is defined in Power BI desktop and applied on Power BI Service.

• Step 1: In Power BI desktop, select table where you have applied custom filters and go to its context menu.Context Menu 

• Step 2: Select Incremental Refresh option that will pop up Incremental Refresh window:
  • Select Incremental refresh table (where you have applied custom filters).
  • Create rows storage policy.
  • Create rows refresh policy.Incremental Refresh

Now publish the report to Power BI service, configure gateway connection (that you already know. If not check out other blogs!!) and enter data source credentials and refresh.Publish to web

And you're done! Everything looks pretty clean and simple doesn’t it?

I hope now you know more about Incremental Refresh in Power BI and its setup 😊.

Thank You

[Anonymous]

Hi Kumail ,
Thanks for the post.
Currently incremental refresh works for the data was updated in , say 10 days. It does not take into consideration the Primary Key that might have been inserted in the past.
Eg: lets say a "Sales Order" was created 3 months , but only today its status is moved to "Shipped". Since I am only looking for past 10 days data for incremental refresh, a new row gets inserted into the Dataset causing my Sales Order to be duplicated. How can we avoid this?

Thanks again for your help

[DebbieE]

Im a little confused with 

• Name new parameter “RangeStart” (Case sensitive), select type “Date/Time”, suggested values “Any Value” and current value “12/29/2010 12:00:00 AM” (first date of your period).
• Create another parameter “RangeEnd” (Case sensitive), select type “Date/Time”, suggested values “Any Value” and current value “12/30/2011 12:00:00 AM” (last date of your period)

What does period mean in this case? 

[Kumail]

Hi @DebbieE 

Thank you for getting in touch.

RangeStart and RangeEnd are custom parameters. These are created to filter rows that apply to that range.

You can create custom filters from the "Manage Parameters" option from the "Home" tab in query editor.

“RangeStart” is the name given to the new parameter. You can give any name you like. Other fields to create parameters are select "Type", "Suggested Values" and "Current Value". You can select/fill that according to the column values where you would be applying this parameter.

In this scenario, I am applying this to Date/time column, therefore, selected

-Type “Date/Time”,

-Suggested Values “Any Value” and

-Current value “12/29/2010 12:00:00 AM” (first date of your period) 

I hope this gives you better understanding of custom parameters used for incremental refresh.

Best Wishes,

Kumail Raza

[answeriver]

Hello,

Thanks for the post.

One thing which i found, column in data need in format date and time.

And one question, is incremental refresh work with published report and nor work in desktop version?

[Kumail]

Hello,

Thank you for getting in touch. 

"One thing which i found, column in data need in format date and time." RangeStart and RangeEnd have date/time format. 

"And one question, is incremental refresh work with published report and nor work in desktop version?"

Refresh policies are defined in Power BI Desktop, and are published to Power BI Service.

Best Wishes,

Kumail Raza

[DebbieE]

Both rangeStart and RangeEnd are Date Time.

And its not working in Power BI Service 'DataSets' 

 I have added a support ticket for this though as it seems to be happening to a few people

[answeriver]

@Kumail 

Sorry but don't understand you.

If i made all of need things (policy, setup of data) is incremental refresh will work on power bi Desktop?

[DebbieE]

Sorry, I'm talking about the Feb 2020 Updates for incremental processing

Everything is set up as is in Power BI desktop but the data set errors in Service and thats because there is a known issue (As I have just found out) and it should be resolved in April

[Kumail]

@answeriver @DebbieE 

Good to hear this. 

Best wishes, 

Kumail Raza

[jader3rd]

I have a question about the Data sources and query folding. What are some examples of data sources which don't query fold?

I ask, because right now my data sources are ADLS Gen1, and I have to have a job that prestages the dataset for Power BI to ingest everyday. I would much prefer it if I could have Power BI look for a range of files to injest following a directory\%Year\%Month\File-%Year-%Month-%Day pattern.

[Anonymous]

@Kumail Thanks for NOT answering my question. I guess not one has answers to this queston! There is no way to ensure my older data get replaced with newer changes.

[Kumail]

Hello @jader3rd

The following gives sources that support query folding. 

https://docs.microsoft.com/en-us/power-query/power-query-folding

Regards

Kumail Raza

[ZikoPowerBI]

Hi ,

Thanks fo the details , I have a question on the below statement:

Create another parameter “RangeEnd” (Case sensitive), select type “Date/Time”, suggested values “Any Value” and current value “12/30/2011 12:00:00 AM” (last date of your period)

Question :- End / Last date in the database will keep on changing as days go by. for eg - today the last date in the dataset is 6th may 2022 in my case and when I implemented Incremental load it was 5th may 2022. When I refreshed my dataset in Power BI service it did not show data for 6 th may 2022 as my parameter RangeEnd has current value as 5th May 2022. Can you please help me understand how will the RangeEnd Parameter work?

[Kumail]

Hello @ZikoPowerBI 

You can use DateTime.LocalNow() function of M language.

Or in detail, you can set following in Advanced editor in in power query.

let

    //Set the following variables

    Culture = "English (United States)", //Select a culture.

    UseYesterdayAsCurrentDate = true, //true = yesterday, false = today

    YearsBack = 2, //How many years to include prior to the current year.

    YearsAhead = 2, //How many years to include after the current year.

    GoToBeginning = "Year", //Options: Year, Month, None

    GoToEnd = "Year", //Options: Year, Month, None

    //Figure the Start and End Dates, based on above variables

    DateToday = DateTime.Date(DateTime.LocalNow())

Hope this helps.

Regards

Kumail Raza