1. Parameters and incremental refresh
OK, time to get our hands dirty again. We want to create an incremental refresh process, but for this, we require some date/time parameters. So let's tackle Power BI parameters first.
In order to create a new parameter, we have to go into the Power Query Editor.
As soon as the window loads, we can see the section Parameters in the Home tab, and an option called Manage Parameters. Let's click on the downward arrow in order to see the options available. Great, so here we find the New Parameter option that we can use to create parameters. Let's click on that.
In the window that appears we are able to set the different attributes of our parameters. In order to set up the incremental refresh, we have to create two parameters using the reserved, case-sensitive keywords - RangeStart and RangeEnd.
The parameters must have the data type Date/Time. They are required in order to filter the data loaded into the Power BI Desktop model table so that only the rows that fall within the date/time period set are included.
Let’s first set the RangeStart to 01/01/2010. The data in our table starts from around 2011, and as we haven’t got a large dataset we can include all the rows as part of our incremental refresh process. We can set the RangeEnd to 01/01/2022. Once both have been set up we can click OK.
We can now actually see the two parameters that we created in the Power Query Editor window if we look at the Queries panel. Using these parameters, we now use them to filter our data.
We click on the WWI_FactSale table and go to our date column of choice - the Invoice Date Key column. We make sure the column datatype is Date/Time and then click on the dropdown menu.
From here we select the Date/Time filters and choose the Custom Filter option. In the new window that appears we are able to set two filters on one column.
In the first filter, let's select the is after or equal to option. In the dropdown menu to the right of this, we are able to select parameters. This will allow us to filter by any parameters we have set up. Now we can select the RangeStart parameter from the dropdown list.
Now for the second filter. Let's use the is before or equal to this time, and base it on the RangeEnd parameter that was created, then click OK.
Now we are back in the Power Query Editor, we have created our parameters and used them to apply filters to our data model. We can click on Close&Apply to continue with the incremental refresh setup.
Once back in the Report view, we can right-click on the table we wish to set up the incremental refresh on.
After selecting Incremental Refresh, we will get the following popup window. Power BI will provide guidance with setup features through messages within different windows.
First, we check the table selected. Then we can toggle step 2 to on.
Now we can set the time period that we wish Power BI to archive data that is in the data model. We can choose days, months, quarters, or years from here.
Let's set it to 5 years for now.
The next step is to select the time period prior to the refresh date that we want the data to be refreshed. Let's enter 30 days here. Basically, all rows with dates in this period will be loaded into the dataset each time a manual or scheduled refresh operation is performed in Power BI Service.
We will also check the box next to the setting Only refresh complete days. This will ensure that only full complete days are included in any refresh process.
At the bottom, we can actually see a timeline of the incremental refresh we have just set up. This helps us visualize what data is archived and what data is incrementally refreshed. Once we apply, the new settings will be updated within our published report in Power BI Service.
Excellent, your turn to try now.
2. Let's practice!