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.

Claim your free eBook

Power Query – Data Refresh

Power Query - Refresh Data

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 Icon
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 

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 - Import Data

Power Query will open a new window and display a sample of the data.  Click Transform Data.

Power Query - Import data window

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).

Transform Date to Month End

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).

Power Query - Remove Columns

Now let’s pivot the data on the Date column.  Select the Date column header, then click Transform -> Pivot Column on the ribbon.

Power Query Pivot Data

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.

Pivot Column Window

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.

Close and Load to Excel - Table

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.

Refreshed Data with new column

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.

Refresh a single query

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

Data Queries and Connection

Each query in the Queries and Connection menu has a refresh icon.  Simply click the icon to refresh the data.

Refresh Query from Queries and Connection - Icon

Alternatively, we can right-click on the query and select Refresh from the menu.

Queries and Connection - Right Click Refresh

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

Data Refresh Connection Properties

The Query Properties window will open

Query Properties Window

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.

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.

Claim your free eBook

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:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. 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.
  4. 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:

27 thoughts on “Power Query – Data Refresh

  1. Chris Botha says:

    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?

    • Excel Off The Grid says:

      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.

  2. SlothB77 says:

    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.

  3. Francis Ong says:

    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.

    • Excel Off The Grid says:

      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.

      • T. Tam says:


        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?


        • Excel Off The Grid says:

          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?

  4. Andrew Gilbertson says:


    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”

    • Excel Off The Grid says:

      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.

  5. Anshul says:


    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?


    • Excel Off The Grid says:

      I’m not certain as I’ve not had to refresh anything that often. Maybe removing all the privacy settings will prevent that.

  6. Cherry says:

    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.

  7. Richard Hammersley says:

    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,


  8. Thomas says:

    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,

    • Excel Off The Grid says:

      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.



  9. Bekki says:

    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.

    • Excel Off The Grid says:

      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.

  10. Liam White says:

    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?


  11. rafa says:

    Thanks for this article!

    I have a Python code that updates and exports a csv file to a folder. I got the data to power query from this folder but when it is updated (through the python code) the powerquery (even after refreshing) remains with the old version. How can I fix it?

    • Excel Off The Grid says:

      Hi Rafa – Sorry, but Python isn’t one of my main languages. So, it’s difficult for me to comment or test.

      Maybe another reader can help?

  12. Jean chiam says:

    Hi Mark,
    My power query started to hang when I click the queries. It start only last week. My files are all in share point/one drive. But even if I bring it to own desktop it will still hang unless I redo it. any way to fix this? Thanks

    • Excel Off The Grid says:

      If it’s suddenly started from nowhere, it sounds like a bug within Power Query (I’ve heard a few people report similar issues recently). Use the “Give Feedback” option within Excel to let them know there is an issue.

Leave a Reply

Your email address will not be published. Required fields are marked *