Introduction
I just read a tweet that was part of a thread started by Microsoft Excel in which someone said they wanted to create a query without loading it to a WorkSheet. Then they mentioned something to do with database technology that meant nothing to me. However, The purpose of this page is not to show you how to create a Connection Only Query in Power Query but how you can use a connection only query as the Source Data for a pivot table.
What we are Aiming at
Here is a screenshot of a file in which there is one source Excel table, a series of connection only Query Tables and a Pivot Table whose data source is a connection only table ... it can be done!
How to do it!
Again, the purpose of this page is not to demonstrate the Power Query aspects of this example but to concentrate on how to use a connection only query as the source of a pivot table.
With your cursor anywhere in your Excel file ... an empty cell is probably the best place! Insert ... Pivot Table ...
Clicking on the Pivot Table icon will open this dialogue box for you:
Notice that I have highlighted the three things that you have to do here
- Tell Excel to use a connection from an external data source ... see the next sub section, Use External Data Source, for this step
- Tell Excel where you want to put the Pivot Table ... I have chosen to put it on the existing worksheet, starting in cell G1
- Tell Excel to do it when you have finished ... OK!
Use External Data Source
In the previous section we saw that we needed to tell Excel which external data source to use in setting up the Pivot Table. Click on the Choose Connection button to see the following ...
Excel helpfully lists all of our Queries, whether connection only or not. In this case, the Locations Query is connection only ... you can see it in the panel on the right in the first screenshot, above. The second query has a very similar name and that is a Loaded Query ... that is shown at the bottom of that panel in the first screenshot and you might just be able to see that is says 37 Rows Loaded ... loaded not connection only.
I have also highlighted the other connection only Queries that are available here; but I have selected Locations and clicked Open.
Now I can click OK to create my Pivot Table.
The Pivot Table
We have now created a Pivot Table and from now on, everything is as normal and we can do what we normally do with a Pivot Table! For example:
Blank Pivot Table
Valid but Uninspiring Pivot Table
Using a Slicer to Great Effect
And finally, Slicer and Pivot Chart
Last word ... And it Still Works Without Access to the Source Data
When demonstrating this feature, Matt Allington said: The last thing I did was move the original source file so that I could be sure that the Power Query Workbook wasn’t actually reading the data from this file when I was interacting with the Pivot Table. I was able to fully interact with the Pivot Table in the Power Query workbook without having access to the underlying source data – exactly the way it works with Power Pivot. See: Traditional Pivot Tables using Power Query - Can Do - Excelerator BI
I did not do what Matt did since he has already proven the concept but you can try it for yourself!
Download: the file I have used here shows a file that I downloaded from this very useful page from Mynda Treacey at myOnlineTraninghub so it is not mine to share: everything I demonstrate here, is my own work, however!.
Duncan Williamson
16th January 2021
No comments:
Post a Comment