Incremental Refresh

• 2021-02-15
• Daniel Otykier

Datasets hosted in the Power BI service can have Incremental Refresh set up on one or more tables. To configure or modify Incremental Refresh on a Power BI dataset, you can either use the XMLA endpoint of the Power BI service directly, or you can use Tabular Editor connected to the XMLA endpoint, as described below:

Setting up Incremental Refresh from scratch with Tabular Editor

1. Connect to the Power BI XMLA R/W endpoint of your workspace, and open the dataset on which you want to configure Incremental Refresh.
2. Incremental refresh requires the RangeStart and RangeEnd parameters to be created (more information), so let's start by adding two new Shared Expressions in Tabular Editor:
3. Name them RangeStart and RangeEnd respectively, set their Kind property to "M" and set their expression to the following (the actual date/time value you specify doesn't matter, as it will be set by the PBI service when starting the data refresh):

#datetime(2021, 6, 9, 0, 0, 0) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true]

4. Next, select the table on which you want to enable incremental refresh 5. Set the EnableRefreshPolicy property on the table to "true": 6. Configure the remaining properties according to the incremental refresh policy you need. Remember to specify an M expression for the SourceExpression property (this is the expression that will be added to partititions created by the incremental refresh policy, which should use the RangeStart and RangeEnd parameters to filter the data in the source). The = operator should only be applied to either RangeStart or RangeEnd, but not both, as data may be duplicated. 7. Save your model (Ctrl+S). 8. Right-click on the table and choose "Apply Refresh Policy".

That's it! At this point, you should see that the Power BI service has automatically generated the partitions on your table, based on the policy you specified.

The next step is to refresh the data in the partitions. You can use the Power BI service for that, or you can refresh the partitions in batches using XMLA/TMSL through SQL Server Management Studio, or even using Tabular Editor's scripting.

Modifying existing refresh policies

You can also use Tabular Editor to modify existing refresh policies that has been set up using Power BI Desktop. Simply follow step 6-8 above in this case.

Applying refresh policies with EffectiveDate

If you want to generate partitions while overriding the current date (for purposes of generating different rolling window ranges), you can use a small script in Tabular Editor to apply the refresh policy with the EffectiveDate parameter.

With the incremental refresh table selected, run the following script in Tabular Editor's "Advanced Scripting" pane, in place of step 8 above:

var effectiveDate = new DateTime(2020, 1, 1);  // Todo: replace with your effective date
Selected.Table.ApplyRefreshPolicy(effectiveDate);