Power Query: Total Refresh – Part 1

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!