Using a Query as a Pivot Table Data Source

Guides • Data Integration & ETL • 25/02/2018

Overview

Here we explain how Queries can be used as a Data Source for Pivot Tables

Combining Power Query with Pivot Tables

When Power Query is used as a data source to Pivot Tables, it can confer some great advantages.

Let us assume you have some data in the source spreadsheet (or anything else that Power Query can point to).

A worksheet that contains 1 million rows of random data made up of transactional data might look like this:

There are no formulas in this workbook, just data – just as if it were exported from a transactional sales system. This sample workbook is almost 27 MB in size, shown below.

Steps

1. Connect with Power Query

Create a new blank workbook and connect this new Workbook to the above workbook using Power Query.
When prompted in Power Query, select Close and Load to and select “Only Create Connection”.

Doing this creates the connection to the source workbook, but does not actually bring any data into the new workbook.

After saving this new workbook, you should see that the new workbook is very small (only 13 KB), confirming no data has been loaded – just a connection.

2. Create a Pivot Table

Now, create a new pivot table that uses this new Power Query as the connection.

Go to the Insert tab ➔ Insert PivotTable
When you get to the Pivot Table wizard, select “Use and External Data Source” and then select “Choose Connection” as shown below:

The wizard shows the query that was created earlier.

Select this query, and then click Open

Finally, click OK to add the Pivot Table. Here, the Pivot Table was set up as follows:

When you save this workbook with the Pivot Table, the workbook becomes larger, but it is still much smaller than the original workbook (only 536 KB).

Standard Pivot Table technology compresses the data (into the Pivot Cache) and only materialises the data it needs to populate the Pivot Table. This keeps the file size of the new workbook small.

The result is this new workbook, compressed almost 51-fold over the original!

3. Works without access to the Source Data

You can completely remove original source file so that you could be sure that the Power Query Workbook is not actually reading the data from this file.

When you do this, you should be able to still fully interact with the Pivot Table in the Power Query workbook, without having access to the underlying source data.

The only thing you cannot do is refresh the data source (as you would expect)!