Power Query: Refreshing Challenges on the Web – Part 2

Welcome to our Power Query blog.  This week, I continue investigating refreshing queries in Excel for the Web by looking at an example with a query and a PivotTable.

Last time, I looked at the web-based counterpart of Excel Power Query.  I looked at the Refresh feature in Excel for the Web and how it interacts with queries.  I learned that the Refresh button on the Data tab does not update queries:

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 because it is ‘Connection only’.  The PQ_fromWorkbook query, which accesses another workbook, didn't refresh either:

This message appeared:

The ‘Refresh All’ button refreshed all the queries that have a source from Table / Range within the Workbook.  However, it issued an error because the queries are from sources that Excel Online does not currently support.

This time, I will look at an example where I have a query and a PivotTable, to see how refreshing affects the results.  I will create a PivotTable from the table PQ_fromTable_Range.  I select the data in the table, and on the Insert tab, I select PivotTable.  A pane appears on the right of the screen:

I create the following PivotTable:

I go to the source table (i.e. the source of the query) and add one [1] more customer here with details as shown:

When I use the ‘Refresh All’ button on the Data tab once, the table  PQ_fromTable_Range is updated (so the query is updated), but the PivotTable is not.  To update the PivotTable, I need to use the ‘Refresh All’ button again:

This implies that ‘Refresh All’ will update the query after it updates the PivotTable.  At the point the PivotTable is updated, the query does not have the extra row.  Once the extra row is on the query, then refreshing again updates the PivotTable.  I need to be careful when I refresh data in Excel on the Web!

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