Modifying Incremental Refresh

  • 2023-01-09
  • Kurt Buhler
Applies to:
  • ❌ Desktop Edition
  • ✔ Business Edition
  • ✔ Enterprise Edition

Incremental Refresh Visual Abstract

Incremental Refresh is changed by adjusting the Refresh Policy properties. Depending on what you want to change, you will adjust a different property. A full overview of these properties is here.

Important

Setting up Incremental Refresh with Tabular Editor 3 is limited to dataset hosted in the Power BI Datasets service. For Analysis Services custom partitioning is required.

Change Incremental Refresh

Below is a general description of how you modify an existing Refresh Policy:

  1. Connect: Connect to the model.

  2. Select the Table: Select the table already configured for Incremental Refresh.

  3. Find 'Refresh Policy' properties: In the Properties window, go to the Refresh Policy section.

    Properties of Incremental Refresh

  4. Change the property: Change the Property specified in the below sections, depending on what you want to change. For an overview of all Refresh Policy properties and what they do, see here.

  5. Apply Changes: Deploy the model changes.

  6. Apply Refresh Policy: Right-click the table and select Apply Refresh Policy.

    Apply Refresh Policy

  7. Refresh all partitions: Shift-click to select all partitions. Right-click and select Refresh > Full refresh (partition). You can right-click the table and select 'Preview data' to see the result.

    Refresh All Partitions

Below is an overview of common changes one might make to an existing Refresh Policy:

Extend or Reduce the Window for Archived Data

Purpose: Add or reduce the amount of data in the model.

Property: RollingWindowPeriods. Increase to extend the window (more data); decrease to reduce the window (less data).

Note: You can also change the RollingWindowGranularity to make a more fine-grain selection, i.e. from 3 Years to 36 Months.





Extend or Reduce the Window for Refreshed Data

Purpose: Add or reduce the amount of data being refreshed in a scheduled refresh operation.

Property: IncrementalWindowPeriods. Increase to extend the window (more data); decrease to reduce the window (less data).

Note: You can also change the IncrementalWindowGranularity to make a more fine-grain selection, i.e. from 3 Years to 36 Months.





Only Refresh Complete Periods

Purpose: Exclude partial (incomplete) periods from the Rolling Window

Property: IncrementalWindowPeriodsOffset. Set the value to -1 to offset the period by 1, excluding the current period.

Note: You can further offset this window to refresh i.e. only the periods behind the most recent complete period.





Change Incremental Refresh Mode

Purpose: To change from Import to Hybrid tables, or vice-versa.

Property: Mode

Note: Follow the below process to change Incremental Refresh Mode:

  1. Change Mode to the desired value Import or Hybrid
  2. Right-click the table and select Apply Refresh Policy
  3. Deploy the model changes
  4. Shift-click to select all partitions. Right-click and select Refresh > Full refresh (partition). You can right-click the table and select 'Preview data' to see the result.
Note

It is recommended to check that the Rolling Window is appropriately set for the selected Mode. When switching from Import to Hybrid Mode, the latest Policy Range Partition will become the DirectQuery partition. You may wish to opt for a more fine-grain window, to limit the amount of data queried with DirectQuery.





Configure 'Detect Data Changes'

Purpose: To configure that archived data will refresh if the value of a date column (i.e. LastUpdate) changes.

Property: PollingExpression. Add a valid M Expression which returns a maximum date value for a column. All records containing that date will be refreshed, irrespective of their partition.

Note: Follow the below process to configure 'Detect Data Changes':

  1. When the table is selected, in the Expression Editor window, select Polling Expression from the top-left dropdown
  2. Copy in the below M Expression, replacing LastUpdate with your desired column name.
// Retrieves the maximum value of the column [LastUpdate]
// Replace LastUpdate with your own column name
// The data will refresh for any records where the value in this column
//    equals the maximum value in the column across the entire table
let
    #"maxLastUpdate" =
        List.Max(
            // Replace the below with your column and table name
            Orders[LastUpdate] 
        ),

    accountForNu11 =
        if #"maxLastUpdate" = null
        then #datetime(1901, 01, 01, 00, 00, 00)
        else #"maxLastUpdate"
in
    accountForNu11
  1. Right-click the table and select Apply Refresh Policy
  2. Deploy the model changes
  3. Shift-click to select all partitions. Right-click and select Refresh > Full refresh (partition). You can right-click the table and select 'Preview data' to see the result.
Warning

Any records will update if the value equals the maximum value in the column. It does not necessarily update explicitly because the value has changed, or if the value equals the refresh date.





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 'New C# Script' pane instead of applying the refresh policy by right-clicking the table.

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



Effective Date





Disabling Incremental Refresh

Purpose: To disable a refresh policy because it is not needed or the use-case no longer fits.

Property: EnableRefreshPolicy

Note: To disable Incremental Refresh, follow the below steps:

  1. Copy the Source Expression: With the table selected, in the Expression Editor window, select Source Expression from the top-left dropdown. Copy the Source Expression to a separate text editor window.
  2. Disable the Refresh Policy: Change EnableRefreshPolicy to False
  3. Remove all Policy Range partitions: Select and delete all of the Policy Range partitions
  4. Create a new M Partition: Right-click the table and select Create > New Partition. Set the partition kind property to M.
  5. Paste the Source Expression: Copy the Source Expression from Step 6 into the Expression Editor as the M Expression when selecting the new partition.
  6. Apply Changes: Deploy the model changes.
  7. Refresh the Table: Select and right-click the table. Select Refresh > Full refresh (table). You can right-click the table and select 'Preview data' to see the result.