Setting up Incremental Refresh

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

Incremental Refresh Setup Visual Abstract

To set up Incremental Refresh, you must configure a new Refresh Policy for the table. This is easily done by configuring the Refresh Policy properties once EnableRefreshPolicy is set to True:

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.

Configure a New Refresh Policy

  1. Connect to the model: Connect to the Power BI XMLA endpoint of your workspace, and open the dataset upon which you want to configure Incremental Refresh.
  2. Create the RangeStart and RangeEnd Parameters: Incremental refresh requires the RangeStart and RangeEnd parameters to be created (more information). Add two new Shared Expressions in Tabular Editor:
Apply Refresh Policy
  1. Configure the RangeStart and RangeEnd Parameters: 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 Power BI Service when starting the data refresh):
#datetime(2021, 6, 9, 0, 0, 0) 
   meta 
   [
      IsParameterQuery=true, 
      Type="DateTime", 
      IsParameterQueryRequired=true
   ]
Apply Refresh Policy 4. __Copy Partition M Code__: Navigate to the table for which you want to configure incremental refresh. Fold it out and select your partition containing your Power Query M Expression. Copy your code to Notepad, you will need it in step 6.
  1. Enable the Table Refresh Policy: In the 'Properties' window, set the EnableRefreshPolicy property on the table to True:
Apply Refresh Policy

  1. Configure the Table Refresh: Next, select the table for which you want to configure incremental refresh. In the Expression Editor window, Select 'Source Expression' from the dropdown, insert your Power Query M Expression from step 4 and alter the Power Query M Expression such that there is a filter step on the date column for which you will enable incremental refresh.

    An example of one such valid filter step is below:

// The filter step must be able to fold back to the data source
// No steps before this should break query folding
#"Incremental Refresh Filter Step" = 
    Table.SelectRows(
        Navigation,
        each 
            [OrderDate] >= #"RangeStart" and 
            [OrderDate] < #"RangeEnd"
    )

Columns that are of date, string or integer types can still be filtered while maintaining query folding using functions that convert RangeStart or RangeEnd to the appropriate data type. For more information about this, see here

  1. Configure Refresh Policy: 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 partitions 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.

    • Source Expression: The M Expression that be added to partitions created by the Refresh Policy.
    • IncrementalWindowGranularity: The granularity of the incremental (refresh) window.
    • IncrementalWindowPeriods: # periods (of granularity specified above) wherein data should be refreshed.
    • IncrementalWindowPeriodsOffset: Set to -1 to set 'Only Refresh Complete Periods'
    • RollingWindowGranularity: The granularity of the rolling (archive) window.
    • RollingWindowPeriods: # periods (of granularity specified above) wherein data should be archived.
    • Mode: Whether it is standard Import Refresh Policy or Hybrid, where the last partition is DirectQuery.
    • PollingExpression: A valid M Expression configured to detect data changes. For more information about Polling Expression or other Refresh Policy properties, see here.

  2. Apply Model Changes: Save your model (Ctrl+S).

  3. Apply Refresh Policy: Right-click on the table and choose "Apply Refresh Policy".

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. All that's left is to refresh all the partitions.

Refresh All Partitions
  1. 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

Finally, you can configure the scheduled refresh in Power BI Service. Power BI will automatically handle the partitioning of your table. You can always connect to the remote model to view and validate the partitions, i.e. using the VertiPaq Analyzer.

Incremental Refresh with Integer Date Keys

If your date column is Integer data type, use the below in the place of the filter Step 4, above:

  1. Create the custom function: Create a Shared Expression named ConvertDatetimeToInt:
   // A custom M function which will return a DateTime value as a YYYYMMDD integer
   (DateValue as datetime) => 
        Date.Year(DateValue) * 10000 + Date.Month(DateValue) * 100 + Date.Day(DateValue)
  1. Create the filter step: Use the custom function to convert RangeStart and RangeEnd in-line to Integer. The filter step is otherwise identical to if the Date column would be a DateTime column:
let
   // Connect to your data source
   Source = 
      Sql.Database(#"SqlEndpoint", #"Database"),

// Load the table data
   Data = 
      Source{ [Schema="Factview", Item="Orders"] }[Data],

   // Make any transformations that should fold back to the data source
   #"Remove Unnecessary Columns" = 
      Table.RemoveColumns ( 
         Data, 
         {
            "DWCreatedDate", 
            "Net Invoice Cost"
         } 
      ),

   // Add incremental refresh filter step
   //    The filter step must be able to fold back to the data source
   //    No steps before this should break query folding
   #"Incremental Refresh" = 
     Table.SelectRows(
       #"Remove Unnecessary Columns",
         each [OrderDateKey] >= ConvertDatetimeToInt(#"RangeStart")
         and  [OrderDateKey] < ConvertDatetimeToInt(#"RangeEnd")
     )
in
   #"Incremental Refresh"
  1. Proceed as normal with the next steps: You can then proceed with configuring and applying the refresh policy with 'Apply refresh policy' and finally refreshing all partitions. Preview the data of the table after the refresh operations complete to see the result.

Incremental Refresh with String Date Keys

If your date column is of String data type, you should configure your filter step to parse the Date column without breaking query folding. This will vary depending on your source and how the date is formatted. Below is a hypothetical example for an Order Date formatted 'YYYY-MM-DD':

let
   // Connect to your data source
   Source = 
      Sql.Database(#"SqlEndpoint", #"Database"),

   // Load the table data
   Data = 
      Source{ [Schema="Factview", Item="Orders"] }[Data],

   // Make any transformations that should fold back to the    data source
   #"Remove Unnecessary Columns" = 
      Table.RemoveColumns ( 
         Data, 
         {
            "DWCreatedDate", 
            "Net Invoice Cost"
         } 
      ),

   // Add incremental refresh filter step
   //    The filter step must be able to fold back to the   data source
   //    No steps before this should break query folding
   #"Incremental Refresh" = 
     Table.SelectRows(
       #"Remove Unnecessary Columns",
       each 

       // Converts "2022-01-09" to DateTime, for example
       DateTime.From(
         Date.FromText(
           [OrderDate], 
           [Format="yyyy-MM-dd"]
         )
       ) >= #"RangeStart"

       and 

       DateTime.From(
         Date.FromText(
           [OrderDate], 
           [Format="yyyy-MM-dd"]
         )
       ) < #"RangeEnd"      
     )
in
   #"Incremental Refresh"

See also the documentation for the Date.FromText function in Power Query here. Should it not be possible to convert the Date column in-line while preserving query folding, it is also possible to configure incremental refresh with a native query, as described in the section, below.

Incremental Refresh with Native Queries

If you have configured a native query, it may still be possible to configure and use incremental refresh, depending on your data source. To try this for yourself, you need to follow the following steps in the place of Step 4, above:

  1. Author and Save the Native Query: Write your native query in SQL Server Management Studio or Azure Data Studio. Include a placeholder WHERE clause which filters >= a DateTime parameter, and < another DateTime parameter.

    Refresh All Partitions

  2. Replace the Native Query String in the Source Expression: Copy the query and replace the existing query, which will be full of characters like (lf) (line feed), (cr) (carraige return) and (n) (new line). Doing this makes the query actually readable and editable without resorting to the Native Query user interface of Power BI Desktop.

Refresh All Partitions

Replace the above text in the Query parameter to the below, for example:

Refresh All Partitions
  1. Add RangeStart and RangeEnd: Concatenate "RangeStart" and "RangeEnd" inside of the WHERE clause, replacing the placeholder fields and converting the parameters to date with Date.From and to string data types using Date.ToText with the Format option set to "yyyy-MM-dd. Don't forget to include single quotes ' on either side of the concatenation. Below is an example of what the final query would look like:
// Example of a full native query that folds and works with Incremental Refresh
let
    Source = Sql.Database("yoursql.database.windows.net", "YourDatabaseName", 
    [Query="

SELECT
    [OrderDateKey]
   ,[DueDateKey]
   ,SUM([OrderQuantity]) AS 'TotalOrderQuantity'
   ,SUM([SalesAmount]  ) AS 'TotalSalesAmount'
   ,[CustomerKey]
   ,[ProductKey]
FROM [DW_fact].[Internet Sales]
WHERE
   CONVERT(DATE, CONVERT(VARCHAR(8), [OrderDateKey])) 
   >= CONVERT(DATE, '" & Date.ToText(Date.From(#"RangeStart"), [Format="yyyy-MM-dd"]) & "')
   AND
   CONVERT(DATE, CONVERT(VARCHAR(8), [OrderDateKey])) 
   < CONVERT(DATE, '" & Date.ToText(Date.From(#"RangeEnd"), [Format="yyyy-MM-dd"]) & "')
GROUP BY
    [OrderDateKey]
   ,[DueDateKey]
   ,[CustomerKey]
   ,[ProductKey]

"])
in
   Source

  1. Validate the new M Expression: You can attempt to save the changes to the table M Expression prior to enabling the refresh policy, to see if you get the expected results when setting the RangeStart and RangeEnd to specific values. If so, you can proceed as normal; Power BI will be able to handle the partitioning as expected if you configured the steps in Power Query.

    It may not be necessary, but depending on the transformations in the native query, you may also try adding the parameter [EnableFolding = True] as described in this article by Chris Webb.

  2. Proceed as normal with the next steps: You can then proceed with configuring and applying the refresh policy with 'Apply refresh policy' and finally refreshing all partitions. Preview the data of the table after the refresh operations complete to see the result.