Power Query: Total Refresh – Part 2

Welcome to our Power Query blog.  This week, I look at how PivotTables may be created directly from queries.

I have some sales data for my salespeople. 

I have been asked to show this data in a pivoted Table and a PivotTable.  Last week, I created a query SalesIncreases.

I loaded it to the same sheet as the original data and created a PivotTable using the Table SalesIncreases. 

However, when I updated the original data and used ‘Refresh All’ from the Data tab, the PivotTable was not initially updated.  

I had to refresh again to get the changes to go through to the PivotTable:

This week, I am going to remove the need to refresh twice.  I right-click on SalesIncreases in the Queries tab in the ‘Queries & Connections’ window:

I choose to create a Reference query, which I call SalesIncreasesPT:

Since this query uses the query SalesIncreases as the source, it will be updated after SalesIncreases when the ‘Refresh All’ occurs.

I choose to ‘Close & Load To…’ from the Home tab:

This time, I choose to load to a ‘PivotTable Report’, and I choose to create the PivotTable at cell F19:

Before I change the labels, I will test whether this PivotTable needs to be refreshed twice.  Mary’s results for January have changed again, and now the increase is 20.  I change the original table:

I use ‘Refresh All’ from the Data tab:

The Table SalesIncreases updates, but the PivotTable created from the Table has not updated yet.

However, the PivotTable created directly from the reference query SalesIncreasesPT has been updated.  

Creating a PivotTable from the query rather than the loaded Table removes the need to refresh twice.  If I need to see the output Table and the PivotTable, I can create the PivotTable directly from a reference query.  The reference query will be updated if the original query is changed, therefore the Table and the PivotTable will always use the same data.

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