Using Power Query Connections in Pivot Tables

andywt

New Member
Joined
Nov 6, 2015
Messages
8
I use Power Query to massage data from a number of sources. It works great. There are cases where to create the right analysis that I use pivot tables. When I create the pivot table, I select External sources and select the appropriate Query as input. Everything so far works great. The issue is if I select for example external query "Monthly Activity" Excel creates a copy of the query "Monthly Activity (2)". While this works it creates a problem as now I have two different queries to maintain/modify going forward.

In my case, this is a monthly activity so when the new month comes along I change the "Monthly Activity" query to point to the current month's file but "Monthly Activity (2)" is still pointing to last month's file. So now all the queries have to be updated. This would be a minor pain if there were only 2 queries, but in my case there are 4 primary queries that get used in 16+ different pivot tables to produce all the monthly stats. So it becomes a major update every month.

So the question is why is an external queried duplicated instead of just using the original query?? Am I doing something wrong or is there a setting that I have missed?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
sorry but I cannot replicate your problem

I deleted pivots, loaded query table and duplicated queries then i did these two pivot tables

pqpt.jpg


maybe you shouldn't load query into the sheet?
 
Upvote 0
This was just a quick sample to show the issue. My main workbook is 150MB and actually does SQL queries to pull the data. This is starting to look like an Excel bug. I'm using Office 365 ProPlus v 1902 (Build 11328.20146 Click to Run.
 
Upvote 0
Software is always guilty :-D

I wonder why you load QueryTable(s) into the sheet if you creating PivotTables?

anyway, call M$ but there are "amateurs" on the phone/chat ;)
 
Upvote 0
There is much PowerQuery in the equation. Once the initial load is done, OweryQuery transforms the data and joins it with other data connections and the Pivot Tables are used to provide counts of thew transformed data.
 
Upvote 0
Why can't you load the results from the queries into the Data Model? It will easily handle the volume. Then use Power Pivot Tables?
 
Upvote 0
After you create the query - go back into the "Load To" properties and only "Create the Connection", then you can insert or create a Pivot with the connection/data source.

This should alleviate your issue. -- There is no need to load to Table (Worksheet) unless you need to see the data list to manually sift through.

It all matters in how you want to visualize your data.

Hope this helps more.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top