How to use the incremental refresh feature in Excel

Excel queries can handle quite large data loads, but sometimes refreshing the entire dataset isn't practical. For larger data sets and especially when using relative date ranges that always get the most recent records, you can use the incremental refresh feature, which offers several advantages:

• Speedier refresh: When only the recently altered data needs updating, this incremental refresh focuses on data from the last 30 days within the full period.
• Enhanced reliability: Smaller datasets reduce the chance of refresh issues caused by data source or Excel API limitations.
• Reduced resource usage: Refreshing less data decreases the overall memory and resource consumption.

Incremental refresh is currently available in queries that use date as a delimiter, with ascending sorting order. For any data you're checking regularly, it's best to use a relative date range, such as year to date, and use dates as a dimension. This way, once the historical data is filled, the query will refresh only the most recent 30-day period.

During subsequent refreshes, the query filters retrieve rows within the 30-day refresh window. Rows falling within this window get refreshed, while those outside it become part of the historical period, exempt from further refreshing.

Instructions

1. Create a new query or modify an existing one.
2. Select a relative date range, such as Year to date, for scheduled refreshes to retrieve the latest data.
3. On the Dimensions tab, select the Date dimension as one of the dimensions. This will enable the incremental refresh checkbox. Make sure that the primary sorting is set by date.
4. Select the Incremental refresh checkbox.

While the incremental refresh feature works with manual refreshes, you get the most value out of it with scheduled refreshes. If you have scheduled refreshes enabled, they will update only with the last 30 days of data. If you don't have a scheduled refresh set up, go to the Schedule tab and click Add trigger to add a trigger to refresh your report at a time that works for you.

Note that when modifying a query that has the incremental refresh feature enabled, the entire historical period will be updated when the changes are applied.

Troubleshooting

Missing data

Long gaps in refresh times can introduce gaps to your data. For example, if you last refreshed the query with the incremental option enabled in May and run the refresh next time in August, you'll be missing data for June and July. 

To fill in the gaps, disable the incremental refresh feature, run a refresh of data, and then enable the incremental refresh feature again.

Data formatting

When you change the order of the columns, the date format will stay in those cells that had dates. If you see integers formatted as dates after such changes, select the column and apply the needed cell format to it.