Power Query: Total Refresh – Part 1
5 April 2023
Welcome to our Power Query blog. This week, I look at PivotTables created from queries.
I have some sales data for my salespeople.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1679053619.png/f0b9efd221a9bf7600059abe9926f158.jpg)
I have been asked to show this data in a pivoted Table and a PivotTable. I start by extracting the data to Power Query. In the ‘Get & Transform’ section of the Data tab, I use ‘From Table/Range’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1679053842.png/344cc8c34f95f0cedbee46a6c1a411f9.jpg)
I take the defaults and click OK. I call the new query SalesIncreases.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1679053889.png/3f0d4633e31228fcd6ce50690c71de6d.jpg)
Power Query has generated a ‘Changed Type’ step, which I delete, as I am going to be unpivoting the columns. I select Salesperson, and on the Transform tab, I select the ‘Unpivot Columns’ dropdown, and then ‘Unpivot Other Columns’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1679054205.png/dd6daa28f712cb21cdd5b870a7977efe.jpg)
This gives me the data in the format I require, and I can rename the columns:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1679054224.png/a8046462ade6de5505eae461ccb8330e.jpg)
As I am going to be loading this data to Excel, I change the data types. There are a several ways to do this; I choose to select Salesperson and Amount and use the ‘Detect Data Type’ option from the ‘Any Column’ section of the Transform tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1679054247.png/a4d9d1a03979b1beb3b30425b48238ef.jpg)
My data is ready to be loaded:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1679054262.png/434c8925694fe3b2036feebe140c191d.jpg)
I choose the ‘Close & Load To…’ option from the Home tab, so that I can choose where to put the Table:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1679054283.png/5f1115878356ed95500446ca2bfc826d.jpg)
As the requirement is for all the data to appear on the same sheet, I choose cell A9.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1679054300.png/9cd8255a95224bf8e9b14e28f89a284c.jpg)
The next step is to create the PivotTable. I click anywhere in Table SalesIncreases and choose PivotTable from the Insert tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1679054327.png/d17a0d4dbc7e48a0ab02df1785afaba2.jpg)
I choose ‘From Table/Range’ from the dropdown menu:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1679054347.png/cb2d56d3e73a7cd95244cea7e0af278c.jpg)
The ‘Table/Range’ defaults to SalesIncreases, and I choose to put the PivotTable on the ‘Existing Worksheet’ in cell F11:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1679054407.png/4bbc49df00a0b8a374a268666bffecb9.jpg)
I rename the labels and my PivotTable is ready:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1679054426.png/93ca88d8e265ae80ddf32cee35fda6a0.jpg)
I receive news that Mary’s increase for January has changed to 13. I update the source table:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1679054449.png/653a41e7c802e8b34bee0df8e4b28f38.jpg)
I choose ‘Refresh All’ from the Data tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1679054482.png/9c13033a71cad1c0ab051674a3a46d53.jpg)
However, this only refreshes table SalesIncreases and not the PivotTable (it does change the column widths though):
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image16-1679054502.png/d8478e2a512af82df9d51b32f975702c.jpg)
Since the Pivot Table is accessing the data from the Table SalesIncreases, and the PivotTable is updated before the Table, I must press ‘Refresh All’ again in order to update the data in the PivotTable:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image17-1679054527.png/81d7f0df24fc15d3791353565927c7a3.jpg)
Next time, I’ll show a way to solve this issue.
Come back next time for more ways to use Power Query!