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.
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’:
I take the defaults and click OK. I call the new query SalesIncreases.
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’:
This gives me the data in the format I require, and I can rename the columns:
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:
My data is ready to be loaded:
I choose the ‘Close & Load To…’ option from the Home tab, so that I can choose where to put the Table:
As the requirement is for all the data to appear on the same sheet, I choose cell A9.
The next step is to create the PivotTable. I click anywhere in Table SalesIncreases and choose PivotTable from the Insert tab:
I choose ‘From Table/Range’ from the dropdown menu:
The ‘Table/Range’ defaults to SalesIncreases, and I choose to put the PivotTable on the ‘Existing Worksheet’ in cell F11:
I rename the labels and my PivotTable is ready:
I receive news that Mary’s increase for January has changed to 13. I update the source table:
I choose ‘Refresh All’ from the Data tab:
However, this only refreshes table SalesIncreases and not the PivotTable (it does change the column widths though):
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:
Next time, I’ll show a way to solve this issue.
Come back next time for more ways to use Power Query!