Course: Microsoft Power BI Training
Topic: Understanding Incremental Refresh and Its Use Cases
Course Link: Microsoft Power BI Training
Demo Link: Register for a Course Demo
JustAcademy Website: Visit JustAcademy
Incremental refresh is a Power BI feature that refreshes only the portion of a dataset that has changed or falls within a configured refresh period, instead of reloading all historical data during every refresh. It is particularly useful when working with large datasets containing millions of rows, historical transactions, customer records, sales data, or operational logs.
In a traditional full refresh, Power BI may need to retrieve and process the entire dataset each time the semantic model is refreshed. As data grows, this process can take longer and consume more resources. Incremental refresh helps reduce unnecessary processing by dividing data into partitions and refreshing selected partitions according to a defined policy.
For example, a retail company may have five years of sales records. If the business receives new sales transactions every day, refreshing all five years of data daily can be inefficient. With incremental refresh, historical records can remain unchanged while Power BI refreshes only the recent period.
Incremental refresh is a data-refresh strategy in Power BI that divides a table's data into separate time-based partitions and refreshes the partitions required by the configured policy. Instead of repeatedly processing the complete historical dataset, Power BI can process new and recently changed data while preserving older partitions.
The feature is commonly used with date or datetime columns, such as OrderDate, TransactionDate, CreatedDate, or ModifiedDate.
Imagine a company stores sales records from January 2021 through December 2025. The report is refreshed every day in 2026.
| Setting | Example Configuration |
|---|---|
| Historical data retention | Keep five years of data |
| Refresh period | Refresh the last seven days |
| Refresh frequency | Daily, according to the scheduled refresh configuration |
| Historical records | Older partitions are generally not refreshed unless the policy or another operation requires it |
| Recent records | The configured recent period is refreshed to capture new or updated data |
In this example, Power BI can retain historical sales records while repeatedly refreshing the recent seven-day period. The overlap is useful because transactions may arrive late or be corrected after their original transaction date.
| Feature | Full Refresh | Incremental Refresh |
|---|---|---|
| Data processed | Typically processes the complete table | Processes partitions selected by the refresh policy |
| Refresh duration | Can become long as data grows | Can be shorter when only a small portion changes |
| Historical data | Reprocessed during a full table refresh | Older partitions can remain unchanged |
| Large datasets | May require substantial resources | Often more efficient for large, time-based datasets |
| Configuration | Generally simpler | Requires parameters and a refresh policy |
| Late-arriving updates | Can capture them if the full refresh reads the corrected records | Captured only when affected data falls within refreshed partitions or another refresh mechanism handles it |
| Typical use | Small tables or datasets where all data changes frequently | Large tables where changes are concentrated in recent periods |
A partition is a logical section of a table's data. Power BI can divide a large table into partitions based on date ranges, making it possible to refresh selected sections instead of treating the table as one indivisible unit.
Suppose a sales table contains data for 2022, 2023, 2024, 2025, and 2026. A partitioning strategy may divide this data into annual, monthly, or smaller date ranges, depending on the refresh policy and the service's partition-management behavior.
| Partition | Example Data | Typical Handling |
|---|---|---|
| Partition 1 | 2022 sales | Retained as historical data if within the configured retention range |
| Partition 2 | 2023 sales | Retained as historical data if within the configured retention range |
| Partition 3 | 2024 sales | Retained as historical data if within the configured retention range |
| Partition 4 | Recent 2025 data | Refreshed when it falls within the policy's refresh window |
| Partition 5 | Current 2026 data | Refreshed according to the configured policy |
Important: This table is conceptual. Power BI's actual partition boundaries and processing behavior depend on the configured policy, service implementation, and supported capacity.
Two important settings in an incremental refresh policy are the period for which data is stored and the period for which data is refreshed. These settings serve different purposes.
The retention period determines how far back the semantic model keeps data. For example, a company may retain five years of sales data to support year-over-year comparisons and long-term business analysis.
When data falls outside the configured retention range, it can be removed as the incremental refresh policy is applied. Retention should therefore be selected according to reporting requirements, compliance needs, and business policy.
The refresh period determines the recent portion of the data that Power BI refreshes during incremental refresh. For example, a business might refresh the last seven days of transactions on each scheduled refresh.
This approach helps capture recent transactions and corrections without repeatedly processing all historical data. The selected period should account for how long source data may remain incomplete or subject to correction.
| Setting | Purpose | Example |
|---|---|---|
| Retention period | Defines how much historical data to keep | Five years |
| Refresh window | Defines the recent period to refresh | Last seven days |
| Refresh schedule | Defines when the semantic model refresh runs | Daily at a configured time |
The ideal refresh window depends on the source system's update behavior.
RangeStart and RangeEnd are Power Query parameters used to filter a table to a specific date or datetime interval. They are essential for configuring incremental refresh in Power BI Desktop.
RangeStart defines the beginning of a date or datetime interval. Rows with timestamps earlier than the selected starting boundary should be excluded from the filtered interval.
RangeEnd defines the end of the date or datetime interval. Rows at or after this boundary should be excluded when using a half-open interval.
The two parameters allow Power BI to identify the records belonging to a specific interval. During incremental refresh, the service can substitute appropriate date boundaries when processing partitions.
Using a half-open interval, where the start is inclusive and the end is exclusive, avoids duplicate boundary records between adjacent date ranges.
let
Source = Sql.Database("YourServer", "SalesDatabase"),
SalesTable = Source{[Schema="dbo", Item="Sales"]}[Data],
FilteredRows = Table.SelectRows(
SalesTable,
each [OrderDate] >= RangeStart
and [OrderDate] < RangeEnd
)
in
FilteredRowsThis example assumes that the SQL Server connection and table navigation match the actual environment and that OrderDate is compatible with the parameter types. Replace the server, database, schema, table, and column names with your own values.
RangeStart.RangeEnd.Query folding is the process in which Power Query translates supported transformation steps into operations that the source database or data system executes. It is particularly important for incremental refresh because filtering should ideally happen at the source rather than after Power BI retrieves a large amount of data.
Suppose a SQL Server table contains 20 million sales records. If the date filter folds to SQL, the source may execute a query similar to:
SELECT *
FROM dbo.Sales
WHERE OrderDate >= @RangeStart
AND OrderDate < @RangeEnd;The database can then return only the records required for that interval, assuming the query and source support the filter operation efficiently.
Query folding support depends on the connector and the transformations used. If folding is unavailable, incremental refresh may still work in some scenarios, but refresh performance and source-data retrieval can be less efficient. Validate the behavior for the actual source.
Power BI Desktop provides an incremental refresh configuration dialog for supported tables. You configure the date filter first and then define the retention and refresh policy.
| Policy Element | Example Value |
|---|---|
| Table | Sales |
| Date column | OrderDate |
| Store rows from the past | Five years |
| Refresh rows from the past | Seven days |
| Scheduled refresh | Daily, subject to service and capacity limits |
| Validation | Compare source and report totals for selected periods |
The values above are examples only. The appropriate policy depends on data volume, business rules, update frequency, licensing, and source-system capabilities.
It is important to distinguish the first refresh after publishing from later scheduled refreshes.
During the initial refresh, Power BI establishes the data and partitions required by the incremental refresh policy. This may involve processing a substantial amount of historical data, so the first refresh can take longer than subsequent refreshes.
For very large datasets, plan the initial load carefully. Confirm that the source can handle the workload and that refresh duration, gateway resources, capacity, and service limitations are appropriate.
After the initial refresh, later refreshes can process the partitions selected by the policy rather than reprocessing the complete historical dataset. The exact processing workload depends on the policy, partition boundaries, source changes, and service behavior.
| Stage | Typical Behavior |
|---|---|
| First refresh | Loads and organizes the data required by the configured policy |
| Daily refresh | Refreshes applicable recent partitions |
| New period begins | Power BI adjusts partitions according to the policy |
| Old data exceeds retention | Data outside the retention range can be removed as the policy is applied |
| Older records are corrected | The changes may require a broader refresh or a different change-handling strategy if they fall outside the refresh window |
Some Power BI incremental refresh configurations support change detection. This can help determine whether data in a partition has changed, so that unchanged partitions do not need to be refreshed unnecessarily in supported scenarios.
Change detection uses a designated date/time column to identify when records have been modified. For example, a source table might contain a column called LastModifiedDate.
| Record | LastModifiedDate | Meaning |
|---|---|---|
| Order 101 | 2026-10-19 09:30 | Record was last modified on the displayed date and time |
| Order 102 | 2026-10-20 14:15 | Record was modified more recently |
| Order 103 | 2026-10-20 16:45 | Record was modified later that day |
In a supported configuration, Power BI may use a modification timestamp to determine whether a partition needs to be refreshed. The exact result depends on the configured policy and the reliability of the source's modification metadata.
Some Power BI incremental refresh configurations support real-time data by combining imported historical partitions with a DirectQuery component for the latest data. This approach is commonly associated with hybrid tables in supported environments.
In Import mode, data is loaded into the semantic model. Reports can query the imported data efficiently, but the information reflects the last successful refresh rather than automatically reflecting every source change.
In DirectQuery mode, Power BI sends queries to the underlying data source when users interact with relevant report visuals. Performance depends on the source, query complexity, network, and capacity.
A hybrid table can combine historical data stored in imported partitions with a DirectQuery partition for the latest data. This can provide fast access to historical information while allowing supported reports to retrieve recent data directly from the source.
| Approach | Advantages | Considerations |
|---|---|---|
| Import | Fast interactive analysis for many workloads | Data freshness depends on refresh frequency |
| DirectQuery | Can access current source data without importing the entire table | Depends on source performance and query design |
| Hybrid | Can combine historical import performance with access to recent source data | Requires supported configuration and careful validation |
Real-time incremental refresh options have specific licensing, capacity, storage-mode, and source requirements. Confirm current platform support before designing a production solution.
Retail and e-commerce organizations often collect large volumes of sales transactions every day. Historical sales records are needed for trend analysis, seasonal comparisons, and year-over-year reporting, while new transactions arrive continuously.
Example: Retain five years of sales data and refresh the last seven days. This can capture recent orders and delayed updates without repeatedly loading every historical transaction.
Finance teams work with invoices, payments, journal entries, and transaction histories. Incremental refresh can reduce repetitive processing for large transaction tables.
Example: A financial report stores several years of transactions but refreshes recent data daily. The finance team must also define how to handle late adjustments, backdated entries, and audit requirements.
Customer activity records may include purchases, support requests, subscription events, and website interactions. New activity is added continuously, while historical data supports retention and segmentation analysis.
Example: Refresh the latest 14 days of customer activity while retaining multiple years of historical records.
Inventory systems generate stock movements, warehouse transfers, and replenishment transactions. Incremental refresh can help process large movement tables efficiently.
Example: Refresh the latest three days of stock movements while preserving historical movements for supply-chain analysis. Current inventory balances may still require special handling if older transactions can be corrected.
Websites and applications can produce millions of event records from clicks, page views, sessions, errors, and user interactions.
Example: Store 12 months of event data and refresh the most recent two days, depending on the source's processing delays and correction policy.
Connected devices generate time-series data such as temperature readings, machine status, energy usage, and equipment performance.
Example: Retain a defined period of sensor history and refresh the latest interval to capture delayed device uploads.
HR systems store employee events, attendance records, recruitment activity, and payroll transactions. Some tables grow over time and may be suitable for incremental refresh.
Example: Refresh recent attendance records while retaining historical records for workforce trend reporting, subject to access control and data-retention policies.
Logistics organizations analyze shipments, deliveries, order status changes, and warehouse activity. A rolling refresh window can help capture updates to recent shipments.
Example: Retain two years of shipment history and refresh the latest 30 days if shipment statuses can change for several weeks after dispatch.
Incremental refresh is especially valuable when a table is large, historical data changes infrequently, and new or modified records are concentrated within a predictable time window.
Determine how many records are added each day, week, or month. Estimate future growth and identify the tables that consume the most refresh time and storage.
Ask the data owners when records become final, how long corrections can occur, whether historical records are deleted, and whether updates modify a timestamp. These answers determine the appropriate refresh window.
Choose a retention period that satisfies reporting needs and business rules. Do not retain unnecessary data indefinitely, but do not remove history required for comparisons, audits, or regulatory obligations.
Choose a window wide enough to capture late-arriving data and recent corrections. A window that is too short may leave the model inconsistent with the source.
Use efficient filters, suitable data types, and appropriate database indexes where beneficial. Confirm that the source can execute the date-range query efficiently and that Power Query folding works as expected.
Problem: The incremental refresh configuration cannot identify the expected parameters or the table filter is not configured correctly.
Solution: Verify that both parameters exist, use the exact names RangeStart and RangeEnd, have compatible Date/Time types, and are referenced in the table's date filter.
Problem: The source column and parameter types are incompatible, or implicit conversions affect filtering.
Solution: Confirm the source column's type, convert values only when necessary, and test the filter with representative boundaries.
Problem: The query retrieves too much data or takes much longer than expected.
Solution: Inspect the transformation sequence, move the date filter earlier when appropriate, remove unnecessary non-folding transformations, and validate the source query.
Problem: The first refresh processes a large amount of historical data.
Solution: Review the retention policy, optimize source queries, assess capacity and gateway resources, and plan the initial refresh during a suitable maintenance period.
Problem: New records do not appear in the report after a refresh.
Solution: Check the refresh history, source filters, time zones, date boundaries, data-source credentials, gateway connectivity, and the configured refresh window.
Problem: A record was corrected in the source but Power BI still displays its old value.
Solution: Determine whether the record is outside the refreshed range. Consider a longer refresh window, supported change-detection configuration, a targeted partition refresh where appropriate, or a separate process for historical corrections.
Problem: Rows around the beginning or end of a date interval are missing or duplicated.
Solution: Use consistent half-open interval logic, such as greater than or equal to RangeStart and less than RangeEnd. Verify the source column's precision and time-zone interpretation.
Problem: The model refresh fails after deployment.
Solution: Review the error message and refresh history. Check credentials, gateway configuration, source availability, capacity limits, query timeouts, and whether the published model supports the selected configuration.
This project demonstrates how a Power BI developer can configure incremental refresh for a sales table.
Create a sales report that retains five years of sales history while refreshing only the most recent seven days during scheduled refreshes.
| Column | Data Type | Description |
|---|---|---|
| SalesID | Whole Number | Unique sales transaction identifier |
| OrderDate | Date/Time | Date and time of the order |
| ProductID | Whole Number | Product identifier |
| CustomerID | Whole Number | Customer identifier |
| Quantity | Whole Number | Quantity sold |
| SalesAmount | Decimal Number | Sales value |
| LastModifiedDate | Date/Time | Timestamp of the latest source modification, if maintained |
| Strategy | Description | Best-Suited Scenario |
|---|---|---|
| Full refresh | Reloads or reprocesses the entire table | Small tables or datasets that require complete reprocessing |
| Incremental refresh | Refreshes partitions selected by a policy | Large time-based datasets with mostly stable history |
| Change Data Capture (CDC) | Captures source changes using source-system mechanisms | Systems requiring a reliable change stream or change history |
| ETL/ELT pipeline | Transforms and loads data through a managed pipeline | Complex enterprise data integration |
| DirectQuery | Queries the source during report interactions | Supported scenarios requiring source-query access instead of full import |
| Hybrid table | Combines imported partitions with a DirectQuery partition in supported configurations | Scenarios combining historical performance and access to recent source data |
These approaches can complement one another. For example, a data engineering pipeline may prepare a clean fact table, while Power BI incremental refresh manages the imported semantic-model partitions.
Answer: Incremental refresh is a feature that refreshes selected portions of a table according to a policy rather than repeatedly processing all historical data.
Answer: It can reduce refresh duration, unnecessary source queries, and processing workload for large datasets where historical records change infrequently.
Answer: They are Power Query Date/Time parameters used to filter records to a specified interval and support incremental partition processing.
Answer: Query folding translates supported Power Query transformations into operations executed by the data source. It can improve incremental refresh efficiency by applying filters at the source.
Answer: The retention period controls how much historical data is kept, while the refresh period controls how much recent data is refreshed.
Answer: No. The first refresh may need to load substantial historical data and create the required partitions, so it can take considerable time.
Answer: The change may not be reflected by the regular incremental refresh policy. A broader refresh, supported change-detection approach, targeted partition refresh, or separate correction process may be needed.
Answer: Yes. SQL Server is a common source for incremental refresh when the query and connector support the required date filtering and efficient execution.
Answer: Change detection is a supported configuration that can use a modification timestamp to help determine whether data has changed and whether a partition needs refreshing.
Answer: In supported configurations, incremental refresh can be combined with a DirectQuery partition in a hybrid table to provide access to recent source data alongside imported historical data.
Answer: A correct date-range filter using RangeStart and RangeEnd is essential, along with a suitable date column and a refresh policy configured for the table.
Answer: Review refresh history, validate recent and historical totals against the source, monitor refresh duration, and inspect partition processing where the available tools and permissions allow it.
Incremental refresh is an important Power BI optimization for large datasets that contain substantial historical data and receive regular updates. By configuring a suitable retention period, refresh window, RangeStart and RangeEnd parameters, and efficient source-side filtering, developers can reduce unnecessary data processing and improve refresh efficiency.
Successful implementation requires more than enabling a setting. Developers must understand the source's update patterns, preserve query folding where possible, plan for late-arriving data and historical corrections, validate results, and monitor refresh performance after deployment.
With careful design and testing, incremental refresh can help create scalable, reliable Power BI reports for sales, finance, inventory, customer analytics, logistics, and other data-intensive business scenarios.
