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 efficientresource consumption and fast refreshes on very large datasets. This feature is available with Power BI premium tenant only.
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?
Filter data set using power query editor. It requires setting up 2 parameters;
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
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