DP-600: implement Incremental Refresh in Power BI/ Fabric

I will teach how to implement Incremental Refresh in Power BI datasets (in the context of Fabric). This skill is common in DP-600: it helps optimize refreshes, reduce time and cost, and is frequently required in production scenarios.

What you need to know

Incremental Refresh allows updating only the subset of data that changed (the refresh window) instead of reloading the entire table. It's especially useful for tables with extensive history (millions of rows) and works with two main components: date filters in Power Query and a refresh policy defined in the model.

Essential prerequisites:

• A date/time column (or version column) to segment the data by period.
• RangeStart and RangeEnd parameters in Power Query that limit the rows loaded.
• A Power BI workspace with Premium capacity (or Fabric), to apply Incremental Refresh policies in the service.

Conceptual example: you store 5 years of history but only need to refresh the last 30 days. You configure the policy to "store" 5 years, "refresh" 30 days — the service reprocesses only the partitions for the last 30 days.

In practice

Practical steps to implement Incremental Refresh (Power BI Desktop → service / Fabric):

1. Create parameters in Power Query: create two DateTime parameters named RangeStart and RangeEnd. Do not assign them a fixed final value — Power BI will replace these on publish.
2. Filter the table by the date column: apply a filter using RangeStart and RangeEnd. The filter must be recognizable as a range filter so the engine can apply partitioning.
3. Publish/Upload to the service: publish the file to a workspace with Premium capacity (Fabric already includes capacity for models).
4. Set the Incremental Refresh policy: in Power BI Desktop, right-click the table > Incremental refresh > set the store and refresh windows, and optionally enable "Detect data changes" specifying the column that signals changes (e.g., LastModifiedDate).
5. Validate: publish and inspect the partitions in the service (in the workspace with capacity). Check the refresh logs and the time consumed.

Minimal M (Power Query) example to apply the filter with parameters:

let
    // Estes parametros devem existir (Type: DateTime)
    RangeStart = #datetime(2023, 1, 1, 0, 0, 0),
    RangeEnd = #datetime(2026, 1, 1, 0, 0, 0),
    Source = Sql.Database("serverName", "dbName", [Query="SELECT OrderDate, Amount FROM Sales"]),
    // Aplicar filtro de intervalo usando os parâmetros
    Filtered = Table.SelectRows(Source, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)
in
    Filtered

Practical notes:

• Create the parameters with type DateTime (not Text).
• Avoid transforming the date column in a way that prevents the initial filter (the filter must occur before transformations that change granularity).
• If you enable "Detect data changes", choose a column that updates when there is a change (Timestamp or incremental identity).

Common errors

1) Filtering after transformation: applying the RangeStart/RangeEnd filter after operations that alter the date column (e.g., splitting, changing time zone) can prevent partition detection — always place the filter early in the Power Query flow.

2) Incorrectly typed parameters: RangeStart/RangeEnd as Text or Date (without time) can cause unexpected behaviors. Use DateTime when your column contains times.

3) Assuming it works in workspaces without capacity: Incremental Refresh requires Premium capacity (or the appropriate Fabric environment). Trying to configure and publish to a workspace without capacity results in errors or a fallback to full refresh.

How to practice

To train this skill safely and aligned with DP-600:

• Use the official Microsoft Practice Assessment (free) to evaluate the exam areas — search for "DP-600 Practice Assessment" on Microsoft Learn.
• Follow the official DP-600 study guide on Microsoft Learn (free) to map this skill to the "skills measured".
• Create a local lab: a sample database (SQL Server/ Azure SQL) with a historical table, set up a Power BI Desktop with RangeStart/RangeEnd, publish to a Fabric/Premium workspace and observe partitions and performance.

These official sources are free and let you confirm you are practicing the actions the exam intends to measure.

In summary

• Incremental Refresh reduces time and cost by updating only the necessary range of the dataset.
• It requires RangeStart/RangeEnd parameters in Power Query, a date/version column and Premium/Fabric capacity.
• Configure the filter early in the ETL, use the correct DateTime and consider "Detect data changes" for delta updates.
• Practice with labs and use the Practice Assessment and the official Microsoft study guide (both free) to validate your preparation.