Question

How can I enable incremental refresh for a dataset with irregular date keys?
I need to set up incremental refresh in Power BI for a dataset where date keys do not follow a continuous sequence. The refresh should correctly partition the data while handling gaps in the date column. What is the best approach to configuring incremental refresh settings to accommodate these irregular date patterns?

Answer 1

To enable incremental refresh for a dataset with irregular date keys in Power BI, follow these steps:

1. Create a Continuous Date Table

Power BI’s incremental refresh requires a RangeStart and RangeEnd filter on a Date/DateTime column. Since your dataset has irregular date keys, create a continuous date table that covers the entire date range, ensuring every possible date exists.

• Use Power Query (M code) to generate a continuous date range:

let  
    StartDate = #date(2020, 1, 1),  
    EndDate = Date.From(DateTime.LocalNow()),  
    DateList = List.Dates(StartDate, Number.From(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),  
    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"})  
in  
    TableFromList  

• Map your irregular date key to this continuous table using relationships or joins.

2. Implement Incremental Refresh in Power Query

• Filter the dataset using RangeStart and RangeEnd parameters:

  1. Define RangeStart and RangeEnd as Date/Time parameters.

  2. Apply a filter on your dataset:

Table.SelectRows(Source, each [DateColumn] >= RangeStart and [DateColumn] < RangeEnd)

• 1. Adjust the filter to include only available data points.

3. Configure Incremental Refresh in Power BI

• After transforming the data, go to Power BI Desktop > Model view and set up incremental refresh:

  1. Right-click the table and choose Incremental refresh.

  2. Define the historical and refresh periods (e.g., keep 2 years of data, refresh the last 1 month).

  3. Enable Detect data changes by selecting a column that tracks modifications (e.g., LastUpdated).

4. Publish & Validate in Power BI Service

• After publishing, Power BI Service will handle partitioning efficiently, only refreshing the latest data while keeping historical data intact.

• Test by refreshing a small range before enabling full refresh.