In Power Query’s properties, in Excel, we can find the “Enable Background Refresh” property, which is usually enabled by default when we create a new query. My goal with this post is to demonstrate what this property does to query refreshing behavior and explain why it is important to pay attention to how we choose to set this property in our Power Query projects.

Where can you find the “enable background refresh” property?

We can find the properties of a query by selecting the query on the Power Query pane, right-clicking on the query, selecting Properties, and then looking into the tab Usage, under the Refresh Control section.

Enable Background Refresh Solve & Excel-08

This property is usually enabled by default by Power Query when a new query is created and the loading destination is a table or “connection only.” When the loading destination is the Data Model, the checkbox for the property “Enable Background Refresh” shows unchecked and the property is disabled, not allowing the user to change that setting.

Why is it important to be aware of the status of the “Enable Background Property” in Power Query?

Let’s look at two different examples.

Example 1: Power Query Tables and Pivot Tables

Case scenario: Every month we receive a CSV file containing information about sales. The file grows month over month, meaning that each new file has the same information as the previous file plus the last month’s data appended to the previous month’s data.

We have an Excel file that has a query built in it to extract the information from the CSV file, loading the sales data into a table. There is also a pivot table based on that table.

When the property “Enable Background Refresh” is enabled:

If this property is enabled and we click the button Refresh All to get both the query table and the pivot table updated with new data, we see that the table gets last month’s data, but the pivot table is not updated with that information as we would expect it to.

Enable Background Refresh property in Power Query in Excel

If we click the “Refresh All” button a second time, then the pivot table also gets updated with the new data values.

Enable Background Refresh property in Power Query in Excel

Why this is happening?

When we have the “Enable Background Refresh” property enabled, we are telling the system that every time we ask it to refresh the Power Query query, we authorize the system to move on with other tasks, without needing to wait for that query refreshing process to be complete.

The pivot table shows a summary of the data in the Power Query table. However, the system is not waiting for the Power Query table to be finished with the data updating before starting with the pivot table’s updating process. Therefore, the pivot table is being refreshed at the same time as the Power Query table is. Because the two processes start around the same time, the pivot table is not updated correctly because the data it has access to at the beginning of the refreshing process has not been updated yet with the most current information.

Then, when we click Refresh All a second time, since we already had the Power Query table updated, this time the pivot table got refreshed with the most current data.

When the property “Enable Background Refresh” is disabled:

If the property “Enable Background Refresh” is disabled, the system will refresh the Pivot Table query first, and when the table is updated the pivot table gets also updated with the new information.

This way, both the Power Query table and the pivot table get refreshed at once, without having to click the button Refresh All twice.

Example 2: Power Query Tables and VBA

Case scenario: Every month, we receive a CSV file with sales data, and we need to change the layout of that data (basically, transforming and reordering columns) and then create a new CSV file with the transformed data.

Enable Background Refresh property in Power Query in Excel

To help us with that task, we built an Excel file with a Power Query query that connects to the original CSV file and takes care of the required transformations.

The Excel file also contains a macro in VBA that instructs Power Query to refresh that query and then creates a new CSV file with the data copied from the Power Query table.

Copy to Clipboard

When the property “Enable Background Refresh” is enabled:

Again, by having the “Enable Background Refresh” property enabled, we are telling the system that every time we ask it to refresh the Power Query query, we authorize the system to move on with other tasks, without needing to wait for that query refreshing process to be complete, including proceeding with running the VBA code.

In this example, if we run the VBA code while the “Enable Background Refresh” property is enabled, we get a CSV file created by the macro that will contain the information that was available in the Power Query table before we started to run the macro, instead of the information acquired after the query was completed.

Enable Background Refresh property in Power Query in Excel

Why this is happening?

Similarly to what happened in the previous example, what is causing this issue is that VBA is carrying on with the procedures the macro is instructing it to do before waiting for Power Query to finish with updating the data information.

When the property “Enable Background Refresh” is disabled:

If we uncheck “enable background refresh” property in the Power Query properties and run the Macro, we will get the correct data in our new CSV file produced by the VBA code in the Excel file.

 

Enable Background Refresh property in Power Query in Excel

This example shows another situation where we do not want to allow Power Query to refresh in the background while the VBA code moves on to completing other tasks. We want the VBA code to wait for the query refreshing process to be complete and only then proceed with the remaining of the instructions.

I don’t know about you, but for me, in most of the situations I deal with, I do not want Power Query to allow background refresh. In my opinion, the ideally would be to have that setting not checked as default.

I posted this idea on Excel’s UserVoice platform asking the Power Query team to make this property not checked by default. If you want to support this idea, please vote for this proposal.