Power Query: Refreshing Challenges on the Web – Part 1

Welcome to our Power Query blog.  This week, I investigate how to refresh queries in Excel for the Web.

This time, let’s take a look at the web-based counterpart of Excel Power Query.  Whilst Power Query in Excel for the Web is being enhanced, there are still differences in functionality when compared with Excel Power Query offline.  This week, I will look at the refresh feature in Excel for the Web and how it interacts with queries. 

Before I start, I will need to create an Excel file offline that already has the queries I want to use loaded, as Excel for the Web currently does not let me create queries (yet!).  If I were looking for places to refresh my query, I might assume that the Refresh button on the Data tab would be a good place to start:

However, this does not work even if I select all of the refreshable regions; it will return this message box instead:

Unfortunately, this Refresh button will only update features like PivotTables.  If I want to refresh a query online, I need to use the Queries button on the Data tab (shown on the screenshot earlier).  On the right-hand side of the screen, the Queries panel appears:

If I use the button highlighted above, which I will refer to as ‘Refresh All’, most of the queries that have a connection to the workbook will update:

I can see that the FilePath query did not refresh.  If I hover the mouse over it, the refresh symbol is greyed out for the FilePath query because it is ‘Connection only’.  This makes sense, as it is not being loaded to the workbook.

When I press the Refresh symbol for the PQ_fromWorkbook query, which accesses another workbook, the process will fail:

This message appears:

Currently, Excel Online only allows the user to refresh data from Table / Range and OData Feed.  Therefore, for PQ_fromTable_Range, I can use refresh successfully, but I cannot use it for PQ_fromWorkbook.  

The ‘Refresh All’ button will work as I might expect now I know the limitations:  it will refresh all the queries that have a source from Table / Range within the Workbook.  However, it will issue an error if I have a query from a source that Excel Online does not currently support.

The ‘Refresh All’ button will work in the Viewing mode of Excel online, which is equivalent to the Read-Only mode of Excel Offline.  However, the Queries button will not.  Thus, in order to view the Queries panel, I must open it in the Editing mode of Excel online and then change to Viewing mode.  I can then access the refresh option for each query or use the ‘Refresh All’ option.

Next time, I will look at an example where I have queries and PivotTables, to see how refreshing affects the results.

Come back next time for more ways to use Power Query!