Excel Power Pivot does not have a built-in incremental refresh policy that keeps old rows and imports only new or changed rows. In Excel for Microsoft 365 on Windows, refreshing a Power Query table loaded to the Data Model generally reruns the query and refreshes that table’s result; to reduce refresh time, filter or prepare data at its source, or use a platform designed for managed incremental refresh.
What incremental loading means
An incremental load changes only the part of a dataset that has changed since the last load. For example, a sales table might contain ten years of transactions, while each nightly update adds yesterday’s sales and corrects a small number of recent records. A genuine incremental process brings in those additions and corrections without reading and replacing the entire ten-year history on every run.
That is different from filtering a query to the last 30 days. A date filter can make a refresh faster because it asks the source for fewer rows, but if the filtered result is the table loaded to Power Pivot, the model contains only those 30 days after refresh. It has not retained the older history and appended the new month to it.
The distinction matters because people often use incremental refresh to describe any refresh that takes less time or fetches fewer rows. In Power Pivot, a query may benefit from source-side filtering or query folding, but those optimizations do not create a stored history of previous query results.
How refresh works in an Excel Data Model
Power Pivot is Excel’s in-workbook analytical data model. Power Query can retrieve and transform source data, and the result can be loaded into the Data Model so that model relationships, measures, and PivotTables can use it. In Microsoft 365 for Windows, use Data > Get Data to connect to a source, then choose Load To in the Power Query load options and select Add this data to the Data Model.
When you choose Data > Refresh All, Excel refreshes applicable workbook connections and queries. A query that loads to the model is evaluated again against its source, and the model table is updated with the query’s returned result. You can also refresh an individual connection from the Queries & Connections pane. Refreshing only one query can save time when other connections do not need updating, but it does not make that query incremental.
For a database query, the source may execute a new statement and return its matching rows. For a workbook or text-file source, Power Query reads the file and applies its steps. The work done can vary by connector and source, but Excel’s local Power Pivot model does not provide the Power BI incremental-refresh policy that partitions a table by date and manages historical and recent partitions for you.
Practical ways to reduce refresh time
If the full history is required in the model, the most reliable improvement is often to reduce work before data reaches Excel, without discarding required records. Consider these approaches:
- Filter at the source. Ask a database for only relevant columns, rows, and date ranges when the analysis permits it. A database can often apply filters more efficiently than Excel can after importing a large result.
- Use query folding where available. Power Query may translate supported filter and transformation steps into a source query. Folding depends on the connector, source, and steps; check the query’s folding indicators or diagnostics rather than assuming every step runs remotely.
- Remove unused data. Drop columns and rows the report does not need. A smaller model can use less memory and refresh more quickly.
- Prepare a clean source table or view. A database view or maintained reporting table can handle joins, exclusions, and calculated fields before Excel imports the result.
- Refresh only what changed. If separate queries represent independent subject areas, refresh the needed connection instead of refreshing every workbook connection. This reduces unrelated work, not the amount of history in that query.
For instance, if a report only analyzes the current and prior fiscal years, filtering out older transactions at the source is a sensible reduction. If the report must compare every year since 2010, filtering to the latest year would make the model incomplete; use a source that can provide the complete history efficiently or adopt an architecture with managed partitions.
A safe date-filter pattern—and its limitation
A rolling date filter can suit a model that intentionally shows only a recent window, such as an operational dashboard for the past 90 days. In Power Query, filter the date column and confirm the expected date range in the preview before loading. After refresh, the table represents the current matching window, not that window appended to earlier contents.
It is also possible to combine an archive with a recent-data query, but the archive must live somewhere persistent outside the refreshed model table. For example, a database may retain closed years in an archive table and store recent transactions separately. A database view can combine both sets for Excel to read. The view must account for corrections, deletions, and duplicate keys; simply appending rows repeatedly to a worksheet is fragile because refreshes can duplicate records or omit updates.
Another workable arrangement is to maintain historical files in a controlled folder and have Power Query combine them with the current file. That can be useful for modest, append-only data, but it is not automatic incremental refresh: Power Query still evaluates the files included by the query. File naming, schema consistency, duplicate handling, and retention need deliberate management.
Steps to choose the right design
- Define what must remain available. Confirm whether users need all historical rows, a fixed reporting period, or only summary totals. This determines whether a date filter is valid or would remove necessary history.
- Identify additions and corrections. A source that only adds new rows is simpler than one that also changes old records or deletes them. Incremental systems need a way to detect updates, not merely rows with a later date.
- Measure the bottleneck. Determine whether time is spent reading the source, transforming data, transferring rows, building the model, or recalculating reports. A smaller query may not help if model processing or a slow source is the main issue.
- Choose where history belongs. Prefer a database, warehouse, or other durable source for a large shared history. Let Excel retrieve the needed reporting result rather than treating a workbook as a database server.
- Test the refresh on a copy. Compare row counts, dates, and totals before and after changes. Confirm that a refresh neither drops history nor creates duplicate transactions.
For a small one-person workbook, a full refresh may be the simplest and safest choice. For a large model refreshed frequently, the time spent engineering a workaround can exceed the cost and maintenance of moving the data preparation to a database or a service with supported incremental refresh.
Common problems and misconceptions
Appending in Power Query is not persistent storage. An Append Queries step combines the current results of two or more queries. It does not remember the rows returned by yesterday’s refresh. If one input is a rolling-window query, the combined result contains whatever that query returns now, not its previous results.
Query folding is not incremental refresh. Folding can push transformations and filters to the source, reducing transferred data or local processing. It does not, by itself, preserve old rows in the Data Model or track which records changed between refreshes.
Refreshing a PivotTable is not the same as refreshing its source. A PivotTable refresh updates its view from the model or connection; it cannot recover source records that were filtered out of the loaded table. Check the query and Data Model contents when totals or dates disappear.
Refreshing on open is still a refresh. A connection configured to refresh when the workbook opens reruns according to its connection behavior. That setting can improve freshness, but it does not add incremental loading or guarantee that an update finishes before the workbook is used.
When to use Power BI or a database instead
If the requirement is specifically to retain historical partitions and refresh only recent data, Power BI has an incremental refresh feature for supported semantic models and sources. It uses date parameters and a refresh policy to define stored periods and the period to refresh. Configuration, licensing, source compatibility, and deployment requirements matter, so verify them for the intended Power BI environment. This feature is not a policy that can simply be switched on for an Excel Power Pivot workbook.
A database or data warehouse can also perform incremental ingestion, such as loading records based on a reliable change timestamp or change-data-capture mechanism, then expose a complete reporting table or view to Excel. This is generally more robust for multiple users, large volumes, corrections to old records, and scheduled refreshes. Excel can remain the analysis and presentation layer while another system owns durable storage and load history.
Power Pivot remains appropriate when the dataset fits the workbook’s practical memory and performance limits and users benefit from a portable model with relationships and measures. The key choice is not simply which tool is faster; it is which system will reliably own the historical data and handle updates.
Frequently Asked Questions
Can Power Pivot append new rows instead of replacing the table?
Not as a built-in incremental refresh behavior for a local Excel Data Model table. Use a persistent source that combines history and new records, or use a platform with managed incremental refresh.
Does Power Query incremental refresh work in Excel?
Power Query in Excel can filter and transform data, but Excel does not expose the Power BI incremental-refresh policy for Power Pivot model tables. A filter can limit the returned rows, but it does not preserve prior refresh results.
Can I load only the last 30 days and keep older rows?
Not by refreshing that filtered query into the same model table. The table will reflect the query’s current 30-day result. Keep the archive in a separate persistent source and combine it with recent data if all dates must remain available.
Will query folding make a Power Pivot refresh incremental?
No. Folding can make the source perform supported operations and may reduce data transferred, but the model still receives the query result for that refresh. It does not automatically track changes or retain earlier results.
Is Power Pivot available on Excel for Mac?
Power Pivot model authoring and management are Windows features in Excel for Microsoft 365. Mac users may have Power Query capabilities that vary by build, but should not assume they can create or manage the same Power Pivot Data Model workflow; check Microsoft’s current feature support for their Excel version.
Related
- Flash Fill in Excel: How It Works and When to Use It
- Flash Fill in Excel: Fill Blanks or Detect Patterns
- Flash Fill in Excel: Find the Button and Use It Correctly
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Dashboard Training: Skills, Courses, and PracticeExcel dashboard training teaches you to turn spreadsheet data into a clear, interactive report using tools such as…
- How to Edit a Stacked Bar Chart in ExcelTo edit a stacked bar chart in Excel, select the chart and use the Chart Design and Format…
- Make Charts in Excel the Same Size: A Step-by-Step GuideTo make several charts the same size in Excel for Microsoft 365 on Windows, select their chart borders…
- How to Insert a Gantt Chart in Google Sheets Step by StepYou can make a Gantt chart in Google Sheets, but Sheets does not provide a dedicated Gantt chart…
- Excel Pie Chart: Group Small Categories into OtherTo group data in an Excel pie chart, first summarize the values you want to show, then chart…
- Excel Pivot Bar Chart: Sort Categories AscendingTo sort an Excel PivotChart’s bar categories in ascending order, sort the matching field in its PivotTable: open…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.