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?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,799
any chance for example excel file with reflected problem?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,799
sorry but I cannot replicate your problem

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



maybe you shouldn't load query into the sheet?
 

andywt

New Member
Joined
Nov 6, 2015
Messages
8

ADVERTISEMENT

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.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,799
Software is always guilty :biggrin:

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 ;)
 

andywt

New Member
Joined
Nov 6, 2015
Messages
8

ADVERTISEMENT

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.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,799
I asked about something different, but ok :) no problem

have a nice day
 

peter789

Board Regular
Joined
Nov 20, 2016
Messages
115
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?
 

davesfx

New Member
Joined
Aug 10, 2011
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,448
Messages
5,528,804
Members
409,837
Latest member
karnasrinivas
Top