Incremental refresh in Power BI - Step-by-Step Guide

Introduction

Incremental refresh helps you update only new or changed data in large datasets. This saves time and makes your reports faster by not reloading all data every time.

When your sales data grows daily and you want to refresh only the latest days.

When you have a large customer database and only new customers need to be added during refresh.

When your report connects to a slow data source and full refresh takes too long.

When you want to reduce load on your data source by limiting data queries.

When you publish reports to Power BI Service and want efficient scheduled refreshes.

Steps

Step 1: Open your Power BI Desktop file

- Power BI Desktop main window

Your report and data model are visible

Step 2: Select the table you want to enable incremental refresh for

- Fields pane

The table is highlighted for editing

Step 3: Click on the Modeling tab

- Ribbon at the top

Modeling options appear

Step 4: Click on Manage Parameters and create two date parameters named RangeStart and RangeEnd

- Modeling tab > Manage Parameters

Two date parameters are created to define the data range for refresh

💡 Set RangeStart to a date far in the past and RangeEnd to today

Step 5: Go to Power Query Editor by clicking Transform Data

- Home tab > Transform Data

Power Query Editor opens showing your data queries

Step 6: Filter the date column in your table to keep rows where date is after or equal to RangeStart and before RangeEnd

- Power Query Editor > Date column filter

Query filters data between RangeStart and RangeEnd parameters

Step 7: Close and apply changes to return to Power BI Desktop

- Power Query Editor > Close & Apply button

Filtered query loads with date range parameters applied

Step 8: Right-click the table and select Incremental refresh

- Fields pane > Table context menu

Incremental refresh settings window opens

Step 9: Enable incremental refresh and set how many days to store and refresh

- Incremental refresh settings window

Incremental refresh policy is applied to the table

Step 10: Publish your report to Power BI Service

- Home tab > Publish

Report uploads and incremental refresh runs on scheduled refreshes

Before vs After

Before

Full dataset of 10 million rows loads every time refresh runs, taking hours

After

Only last 1 day of data loads during refresh, reducing refresh time to minutes

Settings Reference

RangeStart and RangeEnd parameters

📍 Modeling tab > Manage Parameters

Define the date range for filtering data during refresh

Default: RangeStart = 1/1/2000, RangeEnd = Today

Incremental refresh policy

📍 Table context menu > Incremental refresh

Control how much historical data to keep and how much recent data to refresh

Default: Store 5 years, refresh 1 day

Common Mistakes

Not creating RangeStart and RangeEnd parameters before filtering data

Incremental refresh requires these parameters to filter data dynamically

Always create and use RangeStart and RangeEnd parameters in your query filters

Filtering date column incorrectly or not using parameters in filter

Without correct filter, incremental refresh cannot limit data to the date range

Apply filter on date column using RangeStart <= date and date < RangeEnd

Trying incremental refresh on unsupported data sources or local files

Incremental refresh works only with supported sources like SQL Server, Azure, or Power BI datasets

Use supported data sources and publish to Power BI Service for incremental refresh

Summary

Incremental refresh updates only new or changed data, saving time on large datasets.

You must create RangeStart and RangeEnd date parameters and filter your data query using them.

Incremental refresh works after publishing to Power BI Service and requires supported data sources.

Practice

1. What is the main benefit of using Incremental refresh in Power BI?

easy

A. It updates only recent or changed data, saving refresh time.

B. It automatically creates new visuals for the report.

C. It deletes old data permanently from the dataset.

D. It allows editing data directly in Power BI Desktop.

Solution

1. Step 1: Understand Incremental Refresh purpose

   Incremental refresh is designed to update only recent or changed data instead of refreshing the entire dataset.

2. Step 2: Compare options with this purpose

   Only It updates only recent or changed data, saving refresh time. describes this benefit correctly. Other options describe unrelated features.

3. Final Answer:

   It updates only recent or changed data, saving refresh time. -> Option A

4. Quick Check:

   Incremental refresh = updates recent data only [OK]

Hint: Incremental refresh saves time by updating recent data only [OK]

Common Mistakes:

• Thinking it edits data in Power BI Desktop
• Confusing it with visual creation
• Assuming it deletes old data automatically

2. Which of the following is the correct way to define parameters for incremental refresh in Power BI?

easy

A. Create a Boolean parameter named IsIncremental.

B. Create two Date/Time parameters named RangeStart and RangeEnd.

C. Create two Number parameters named StartValue and EndValue.

D. Create a single Text parameter named RefreshDate.

Solution

1. Step 1: Recall parameter requirements for incremental refresh

   Incremental refresh requires two Date/Time parameters named RangeStart and RangeEnd to filter data by date.

2. Step 2: Check options for correct parameter types and names

   Only Create two Date/Time parameters named RangeStart and RangeEnd. matches the required parameter names and types. Others use wrong types or names.

3. Final Answer:

   Create two Date/Time parameters named RangeStart and RangeEnd. -> Option B

4. Quick Check:

   Parameters = RangeStart and RangeEnd Date/Time [OK]

Hint: Use RangeStart and RangeEnd Date/Time parameters for filtering [OK]

Common Mistakes:

• Using wrong parameter types like Text or Boolean
• Naming parameters incorrectly
• Creating only one parameter instead of two

3. Given a table with a Date column and parameters RangeStart = 2023-01-01, RangeEnd = 2023-01-31, what will this filter expression return?

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

medium

A. Rows with Date before 2023-01-01.

B. Rows with Date from 2023-01-01 to 2023-01-31 inclusive.

C. Rows with Date after 2023-01-31.

D. Rows with Date from 2023-01-01 up to but not including 2023-01-31.

Solution

1. Step 1: Analyze the filter condition

   The filter keeps rows where Date is greater than or equal to RangeStart (2023-01-01) and less than RangeEnd (2023-01-31).

2. Step 2: Understand the date range included

   This means dates from 2023-01-01 up to but not including 2023-01-31 are included.

3. Final Answer:

   Rows with Date from 2023-01-01 up to but not including 2023-01-31. -> Option D

4. Quick Check:

   Date >= RangeStart and < RangeEnd excludes RangeEnd date [OK]

Hint: Less than RangeEnd excludes that date, so range is start inclusive, end exclusive [OK]

Common Mistakes:

• Assuming RangeEnd date is included
• Confusing greater than and less than operators
• Ignoring parameter values

4. You set up incremental refresh but after publishing, the dataset refresh fails. Which of these is a likely cause?

medium

A. You enabled incremental refresh but did not publish to Power BI Service.

B. You did not define RangeStart and RangeEnd parameters correctly.

C. You used a Text column instead of Date/Time for filtering.

D. You refreshed the dataset manually in Power BI Desktop.

Solution

1. Step 1: Identify common causes of refresh failure

   Incremental refresh requires a Date/Time column for filtering. Using a Text column causes failure.

2. Step 2: Evaluate options for correct cause

   You used a Text column instead of Date/Time for filtering. correctly identifies using a Text column as a cause. Other options are either incorrect or unrelated to failure.

3. Final Answer:

   You used a Text column instead of Date/Time for filtering. -> Option C

4. Quick Check:

   Filter column must be Date/Time, not Text [OK]

Hint: Filter column must be Date/Time type, not Text [OK]

Common Mistakes:

• Assuming manual refresh in Desktop triggers incremental refresh
• Not defining parameters but still expecting refresh to work
• Forgetting to publish dataset to Power BI Service

5. You want to configure incremental refresh for sales data with a 5-year historical range but only refresh the last 1 month. Which settings should you use in Power BI Service?

hard

A. Set Incremental refresh policy to store 5 years and refresh 1 month.

B. Set Incremental refresh policy to store 1 month and refresh 5 years.

C. Set Incremental refresh policy to store 5 years and refresh 5 years.

D. Set Incremental refresh policy to store 1 month and refresh 1 month.

Solution

1. Step 1: Understand incremental refresh policy settings

   The policy has two parts: how much historical data to store and how much recent data to refresh.

2. Step 2: Match requirements to settings

   You want to keep 5 years of data but only refresh the last 1 month. So store 5 years, refresh 1 month.

3. Final Answer:

   Set Incremental refresh policy to store 5 years and refresh 1 month. -> Option A

4. Quick Check:

   Store = 5 years, Refresh = 1 month [OK]

Hint: Store historical data long, refresh only recent period [OK]

Common Mistakes:

• Swapping store and refresh periods
• Refreshing entire historical data unnecessarily
• Storing only recent data losing history