In the previous part of this Power Query series, we took a first look at how to import data from various file formats and load it into Excel. Now, in this part, we move on to consider how we can refresh the data, which enables us to build a query once and use it over and over.
Download the example file
I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.
Download the file: Power Query – Example Files
Refresh all data
Unlike Excel’s calculation engine, which by default will re-calculate with every change, Power Query will only recalculate whenfahref specifically commanded.
The refresh process is straightforward, click Data -> Refresh All
Let’s see this refresh process in action.
The following uses Example 6 – Data Refresh 1.csv and Example 6 – Data Refresh 2.csv from the downloads.
Create a basic query
Open a new workbook and create a new query using a CSV file; from the Data ribbon, Click Get Data -> From File -> From Text/CSV.
The Import Data window will open. Navigate to the Example 6 – Data Refresh 1.csv file, select it and click Import.
Power Query will open a new window and display a sample of the data. Click Transform Data.
We will now make some basic data transformations to shape the CSV into more useful information. This is the first time in this series we are performing some of these transformations, but don’t worry too much about them now, we will definitely see them again in future sections.
Click on the Date column. From the ribbon, click Transform -> Date -> Month -> End of Month (this will change the Date column to the last day in the calendar month).
Click on the Product column header, then hold the Shift key and click on the Sold By column header. Both columns should now be selected.
From the ribbon click Home -> Remove Columns (this will remove the selected columns).
Now let’s pivot the data on the Date column. Select the Date column header, then click Transform -> Pivot Column on the ribbon.
The Pivot Column window will open. Change the Values Column to Value. Click the Advanced options and set the Aggregate Value Function to Sum. Then, click OK.
Now click Home -> Close & Load. Excel will create a new worksheet with a Table. This table contains the data from the CSV, which has been transformed. It should look like the screenshot below.
Update the source data
To demonstrate the refresh process, we are simulating where a user might receive a new file on a daily, weekly or monthly basis. To demonstrate this, we need to rename both of the example workbooks.
- Example 6 – Data Refresh 1.csv can be renamed to anything you want
- Example 6 – Data Refresh 2.csv must be renamed to Example 6 – Data Refresh 1.csv
Power Query is still pointing to a file called Example 6 – Data Refresh 1.csv, which contains the new data.
Refresh the data
Click Data -> Refresh All.
In the background, Excel will now import the data from the file into Power Query, apply the same transformations and Load the data into the worksheet. The new data should appear automatically on the worksheet.
Did you notice that! The March data appeared with a single click – that is amazing!
Each time there is a change to the existing data, or where a new file is received, it is only necessary to save the file with the same file path, then click one button. That is powerful stuff… right?
In a future part, we will look at linking the file path a cell, so you can import without having to overwrite the previous file.
Refresh only specific queries
When we have lots of queries within a workbook, it can take a while to refresh them all. In these circumstances, it is useful to refresh only the queries we need. There are a few ways to achieve this.
Refresh button
If you click on a Table, which has come from a query, the Data -> Refresh drop-down menu includes an additional option.
Clicking on Refresh will only refresh the selected query.
Queries & connections menus
When we loaded the data from Power Query into Excel, the Queries and Connections menu opened. This window shows all the queries created in the workbook.
If that menu is not open, click Data -> Queries and Connections
Each query in the Queries and Connection menu has a refresh icon. Simply click the icon to refresh the data.
Alternatively, we can right-click on the query and select Refresh from the menu.
VBA refresh
I’m not going to cover it in this series, but you can use VBA to refresh individual queries. This can be a useful option when:
- you want to provide an easy to use interface for the user
- you want to control the order in which the queries refresh
Advanced refresh options
As more queries are added to a workbook, it can soon become time-consuming to refresh individual queries, or too slow to refresh them all. The good news is that Excel already has options to enable the data to be refreshed automatically.
Select a cell within a query Table, then click Data -> Refresh All (Drop Down) -> Connection Properties
The Query Properties window will open
The refresh options available in this window are:
Background refresh – Background refresh allows us to keep working while the data refreshes in the background. It is turned on by default. If we remove this option, we will be unable to use Excel until the refresh process is complete. With background refresh applied, the refresh process itself will take longer, but that might not be a problem as we can keep working.
Refresh every x minutes – The workbook must be open for the “Refresh every x minutes” option to work. It can be useful where the source data is regularly changing. It is probably a good idea only to use this option with the background refresh enabled, or it could become very annoying for users.
Refresh when opening the file – Automatically refreshing the data when opening the file is a useful feature as you know the data is up-to-date whenever you open it.
Refresh this connection on Refresh All – Where a query contains static data, there is no need to refresh it every time. By removing the tick next to “Refresh this connection on Refresh All”, it removes these queries from the Refresh All process, which in turn, reduces the refresh time.
The watch outs!
OK, it’s now time to consider the things which might catch us out.
Closed vs. Open files
The location where the source data is stored is important for data refresh.
When the data is stored in an external file, be it CSV, text or even an Excel Workbook, it is the last saved version of the file which is loaded into Power Query. If the external file is open, the changes will not be included in the refresh until it has been saved.
Where the data and query are in the same workbook, all changes are incorporated even if the file has not yet been saved.
Queries based on other queries
In a future post, we will consider how to use a query within another query. There is nothing wrong with this practice, but we just need to be aware of the consequences if we only refresh a single query, as any subsequent queries in the chain will not be refreshed.
By using Refresh All, the queries are refreshed in the right order. This is not the case when refreshing individual queries, it is up to the user to refresh the right queries at the right time and in the right order.
Background refresh
Background refresh is a great feature as we can keep working in Excel. However, any Pivot Tables or formulas which are updated or calculated before the background refresh has completed will not necessarily have the correct data. Therefore is some circumstances, you may wish to remove background refresh.
Power Query Series Contents
- Introduction
- Import Data
- Data Refresh
- Edit Queries
- Close & Load Options
- Using Parameters
- Basic Transformations
- Combine / Append Queries
- Import All Files in a Folder
- List All the Files in a Folder and File Attributes
- Import Data from the Current Workbook
- Import Data from the Web
- Unpivot Data
- Unstacking Data in a Column
- Lookup Values using Merge
- Change the Source Data Location
- Formulas
- If Statements for Conditional Logic
- Grouping and Summarizing Data
- Custom Functions
- Common Errors & How to Fix Them
- Tips and Tricks
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
Don’t forget:
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
Please help.
I am just starting out with using Power Query.
I have a source file into Power Query from Excel. In the Excel file one of the columns is a calculated field using today() to work out days from a specific order.
I did this yesterday and for example the one answer was 350 days.
I open the Excel file today and it obviously shows 351, but my Power Quiery (even after pressing Refresh All) still shows 350?
Hi Chris,
Based on your description of the issue, I think you just need to save the workbook which contains the days calculation before clicking Refresh. If the data is in a separate workbook to the query, the Refresh will use the last saved version.
Looking for solutions to the problem of Nested Power Query Workbooks. Workbook file A is the source for workbook file B, which is the source for workbook file C. When I refresh the data in workbook C, I want the data refresh in workbook file B to automatically be triggered, sourcing the last saved data in workbook file A. Sequentially, the desired outcome is for workbook C to be refreshed after workbook B is refreshed. Finally, the goal is for this to be done automatically, so users do not have to manually open, refresh and save workbook B before repeating with workbook C.
What is the best way to refresh as and when required in power query which is link to sql database? It is taking a long time.
Only want to refresh it periodically when i want updated data.
Etc. refresh the data once and work with it without having to refresh again.
That’s what Power Query is designed to do already. You want to load the data into Excel, or the Data Model (Power Pivot) and then you can use it as much as you like.
However, if you’re using Power Query to perform the calculations (rather than just transformations), then you will be refreshing on a regular basis.
So you may need to change our approach.
Hey,
Slightly relating to this topic – I have a spreadsheet linked to an external source (cloud server); when i delete entries from the server, it is not reflected on excel. No matter how many times I refresh, the respective column doesn’t seem to delete by itself. Is it a matter of time (as in days?) or is there something in my settings that I can change to make sure it happens fast?
Thanks.
Power Query will go and get whatever data there is. There are no specific settings on the Power Query side which you need to change. When you delete records in the Cloud Server, are they really deleted from the source data, or do they have a separate field that marks them as inactive?
Hi,
I am on Excel 2016 which may explain this, but when clicking on refresh all, I got the error message:
“[DataFormat.Error] We couldn’t convert to number”
Sounds like a data issue to me. You’ve probably got a step where you’re trying to convert a column to a number, but that column contains at least one text value in the source data.
this was very helpful. I specifically needed to know what was the best options to choose in refreshing query data. whether to choose every 60 minutes or upon opening.
Thanks Cynthia – in regard to your question, there is no “best option”, it depends on your scenario.
Hi..
I am having some issue in my excel sheet. I imported data from a website into the sheet and fix with a automatic connection every 5 min, but every 5 min a pop-up opens asking permission to connect. How to fix this?
Regards,
Anshul
I’m not certain as I’ve not had to refresh anything that often. Maybe removing all the privacy settings will prevent that.
I noticed that when I loaded the file Example 6 1, the format of the Date column is Text, not Date. I tried to change it to Date but some rows returned “Error”. Please instruct me how to fix it, thank you.
Hi Cherry – I’m guessing it’s because of your region settings in Excel. As the date format I’ve used (which is for the UK) is not a recognized format for your region. Take a look at this post (unfortunately dates can get overly complicated):
https://exceloffthegrid.com/power-query-date-formats/
Great advice, thanks for putting this together.
My users apply Power Query to SSAS cube data held on Azure. They need to authenticate with a Microsoft Account but I’m unable to set this as the default authentication method. Every user has to change from the default Windows authentication every time they open the query. Can Microsoft authentication be defaulted?
Thanks in advance,
Richard.
Hi, that’s great advice, thanks for the help.
In my case I extract my data from my ERP with Power Querry, everything is fine and working perfectly. But once I have these datas my goal is to add a comment to each line directly on Excel.
What’s the best way to work around it ? If I simply add a comment column on Excel then whenever I refresh the datas it will mess up the lines.
Thanks for your help,
Thomas.
Hi Thomas,
Power Query doesn’t let you add comments after load. However, if each line has a separate unique reference you could create an Excel Table to hold the unique reference and the comment. Then when you refresh you would merge the original Data Table with the new Comments Table. That would get you to a similar end result.
Thanks,
Mark
Just start to use query to retrieve data, and very useful information! But I noticed that whenever refreshing data, new data will change from numeric to text. Can you please kindly help how can I make the columns to remain as numeric.
Hi Bekki – In the query editor at the top of each column there is a button that shows the type of data in the column. Click that button and select a suitable data type. Then when you close and load, it should remain as a number.
Thanks for this article!
Power Query doesn’t seem to function for Excel Spreadsheets saved Online in SharePoint. Do you know of a way to trigger a Power Query refresh for a file saved in SharePoint or can it only be done in the desktop application?
Thanks
Excel online doesn’t currently have the ability to refresh queries.
No worries, thanks for confirming this limitation.