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?
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?