# Using Power Query Connections in Pivot Tables



## andywt (Mar 11, 2019)

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?


----------



## sandy666 (Mar 12, 2019)

any chance for example excel file with reflected problem?


----------



## andywt (Mar 12, 2019)

Here is a sample: https://totherescueoftexas-my.share...BAryKmUncgV7EBj3icUdoCUr4QS9_X2LZAWA?e=Fy2IJy 

The first data connection was created from a Table in the Workbook.  When I inserted a pivot table and chose the external data source "table1" Excel creates a  "table 1 (1)" connection.  If I add another pivot table with the table 1 external source, it creates a "table 1 (2)" connection.


----------



## sandy666 (Mar 12, 2019)

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 (Mar 12, 2019)

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 (Mar 12, 2019)

Software is always guilty 

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 (Mar 12, 2019)

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 (Mar 12, 2019)

I asked about something different, but ok  no problem

have a nice day


----------



## peter789 (Mar 12, 2019)

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 (Mar 29, 2019)

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.


----------



## andywt (Mar 11, 2019)

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?


----------



## andywt (Mar 29, 2019)

In my production Workbook, I am only using Connection Only.  And that is where ther issue occurs.  I believe it is a bug in office365 Excel (64bit) as it seems to be a new behavior.


----------

