Using Power Query Connections in Pivot Tables

[andywt]

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]

any chance for example excel file with reflected problem?

 

[andywt]

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]

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]

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]

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]

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]

I asked about something different, but ok no problem

have a nice day

 

[peter789]

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]

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.