In this writing, I want to share what I consider when configuring incremental refresh in Power BI. Most of cases, I failed or got doubt to set up incremental refresh when I did not properly check the below statements. There might be more than the below to check and consider when building up incremental refresh in Power BI, and I love to hear your experiences when you failed to configure it properly.
I will try to describe and share my experiences here (except some basic situations, for instance, creating parameters with the names like RangeStart and RangeEnd) with examples when incremental refresh did not properly work or when I had doubt.
1. Query Folding - just a warning?
I extracted the data from SQL server like below.
I purposely disabled Query Folding feature by adding a step with using Table.Distinct function in Power Query Editor.
→ I happened to know that Table.Distinct function does not support query folding. So, I used this function in order to create No-query-folding. Please correct me if I am wrong.
Filter the Date column by using parameters like the below figure.
Load to Power BI desktop, and in the Data View tab, right click the table name.
Click "Incremental refresh".
The below pop up window appeared, and the warning message is shown on the top. It says that using non-foldable queries is not recommended. However, I could still configure the incremental refresh. So, I tried to set up like below.
I created a table visualization like below.
Publish it to a workspace,
Once the dataset is refreshed successfully for the first time, and then all the information will be shown like below.
In the source, I added three information like below. In theory, red colored data cannot be loaded, but only green colored information can be loaded and seen after the next refresh.
It shows the data as expected.
As a result, expected numbers are shown even query folding was disabled. However, do not ignore the warning message, and always try to evaluate whether the data is correctly incremental-refreshed and shown on the report.
2. Parameters = Date/Time data type
In this section, I tried to configure incremental refresh by creating parameters as Date data type, not date/time data type. Because in the most situation, I have date column and time column separately, instead of having one column that shows date/time data type.
I used the below sample, and the below sample's query folding is enabled.
After loading the data, and when I tried to configure incremental refresh, as shown in the below figure, I could not do it because the toggle was disabled.
So, I tried another way. I kept parameters as date/time data type, and I filtered the table.
And then I changed the data type to date data type in the last step of the transformation.
After loading it, I could configure incremental refresh like below.
After publishing it, the report is shown like below.
After the first refresh is successfully done, the report is updated like below.
In the source, I added three information like below. In theory, red colored data cannot be loaded, but only green colored information can be loaded and seen after the next refresh.
It shows the data as expected.
As a result, parameters (RangeStart, RangeEnd) have to be date/time data type. Once the column (date/time data type) in the table is filtered, I could change the data type to date data type.
3. "Equal to" on both side? - is after or equal to // is before or equal to
In this case, I am following the same steps as above example, except configuring the filter condition in a slightly different way, like below.
In this test, I liked to check what would happen if "equal to" condition is shown on both area.
Incremental configuration was done, and I published it.
After the first refresh was successfully done, the report is shown like below.
In each year, 1st January's numbers are all doubled, which is not a desired outcome.
As a result, "equal to" condition has to be mentioned only one side when filtering the column in Power Query Editor.
There are more things to carefully consider when configuring incremental refresh in Power BI. But in this writing, I highlighted three points that made me confused or failures when building up incremental refresh.
To summarize, when creating a data model to configure incremental refresh,
1. Query Folding - it works even query folding is disabled, but still need to evaluate that the report shows the correct number.
2. Parameters → "Date/time data type" - Once it is filtered, the column's data type can be changed to date data type from date/time data type in the last step of the transformation.
3. Filter condition - "equal to" condition on one side only. Otherwise, some numbers might be doubled.
I hope this helps to have more fun in checking incremental refresh configuration in your data model.
Comments