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.
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.
If we click the “Refresh All” button a second time, then the pivot table also gets updated with the new data values.
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.
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.
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.
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.
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.
- Learn how to automate your Excel reports
- Get this ready-to-use Excel solution for 2 and 3 columns of Dependent Dropdown Lists
- Check other posts about Power Query and Excel in this Blog,
Howdy I am so grateful I found your site, I really found you by error, while I
was browsing on Digg for something else, Anyhow I am
here now and would just like to say cheers for a marvelous post
and a all round exciting blog (I also love the
theme/design), I don’t have time to browse it all at the minute but I have book-marked it and also added
your RSS feeds, so when I have time I will be back to read
more, Please do keep up the excellent b.
Thank you, Caroline! Welcome to this space. Feel free to leave any suggestions.
This was a well written article. I mostly agree disabling background refresh by default would be ok, except that when dashboards are created by power queries, users don’t always know to click refreshall, and so they might not be looking at the most up to date data. Yes VBA could change that but then they would have to enable macros.
One thing I have investigated by trial and error and have not found, is a way to change this setting using VBA. I can change the BackgroundQuery property. This documentation seems to discuss everything except to tell us what BackgroundQuery actually does.
https://docs.microsoft.com/en-us/office/vba/api/excel.querytable.backgroundquery
Dashboard users will still have to refresh data to get data updated, regardless of whether or not “Enable Background Refresh” property is enabled.
As for the VBA code to change the “Enable Background Refresh” property status please check the following post:
https://www.excelcampus.com/vba/enable-background-refresh-on-all-power-query-connections/
Hi Celia Alves,
I would like to un-check “Enable Background Refresh” but I can’t do it.
How to select “un-check” and next time i open the file it’s still in that mode (Un-Check).
Thanks Celia Alves!
Mr Tuan Le
tuanlecpa73@gmail.com
Hi, Mr Tua Le,
If after you change (uncheck) the “Enable Background Property,” you save the Excel file before closing, the next time it should still show “unchecked.”
It’s an amazing post designed for all the web people; they will get benefit from it I am sure.
I have a table that is created using a few queries/connections. Some of the connections are to Snowflake and some are to Google Sheets. The connections append one table. I have a pivot table based on the data from that one table. When I am selecting different fields from that table in my pivot table Excel starts “Connecting to Datasource” and then “Running background query”. Sorry if I misunderstood but are you saying if I uncheck “Enable Background Refresh” it will stop doing this? It will still refresh the data when I Refresh All but not when I am playing around in the Pivot table?
Hi, Gob.
If I understood correctly, you created a few queries, and loaded a table based on those queries into Excel. Then, from that table, you created a pivot table. If this is correct, when you have new data and you click Refresh All to update the data imported to your spreadsheet, you will probably get the table updated but not the pivot table. then, you click Refresh again, and this time the pivot table will update. Unchecking “enable background refresh” should solve this and will make both the table and the pivot table update with one single click of the button “Refresh All.”
I am not sure what causes the behaviour you describe. It may be related to the connectors you are using. Or maybe you are loading directly to a Pivot Table instead of a Table?…
you assumed we can FIND the Power Query pane! If I knew how to do that I wouldn’t be googling “Where can you find the “enable background refresh” property?
Don, the assumption is that if you need to deal with Background Refresh property in Power Query, you are using Power Query and, therefore, you know where to find its pane.
Glad that you are resourceful enough to find all the information you need for free on the internet. I hope I was helpful in some way and wish you a happy learning journey.
Thank you, Celia. This is really helpful because I am building a small “server” for the team by using vba to refresh workbooks with power queries every 2 minutes. Hope to see more posts from you.
Suppose the workbook is on a sharepoint site. Is there a way to refresh the data, in the background, without opening the workbook?
Hi Celia, thank you for your post, it is really explaining. In my case, I am able to refresh excel table via powerquery triggered with VBA but it does not work for my colleague- he still sees template file which contains old data. And yes, I checked “enable background refresh” and it is same(unclicked) for both of us. He is able to refresh template file manually so it is also not related with permissions. What would you recommend in this case? Thank you in advance.