I create Excel files for co-workers to use with their daily/monthly tasks. In one case, I have created several smaller Excel files with Power Query utilizing connections only and loading output to worksheets. Due to the size and complexity of some of the files, I created one overall Excel file that queries the row-loaded output of these supporting files to get an overall summary. My problem is if the query in one file (“A”) is updated while another file (“B”) is open, the connection-only queries from one file will save to the other file. Per attached PDF, a file that should have only 2 queries has ballooned to 273. The additional queries are from the same file, but occurring over several months.
I have experienced this before, and have instructed my co-workers to have only one file open while refreshing any queries, then save/close the file before opening another file with queries that need refreshing. Obviously this isn’t being followed.
Is this the nature of how PQ works, or is there a setting to change? Any help is appreciated.
Hello,
Difficult issue there. I have not stumbled upon such issue myself, but anyway, something seems to trigger the creation of those other queries. Are you able to share some example files, stripped from real data?
Br,
Anders
Anders, I am discovering the user may be copying a PQ created table (a summary of data) from one sheet to the next, and as a result, is transferring the connections as well. My approach is to fix this copying issue first, and see how my files react. If successful, I will mark this as resolved.
The very same thing was happening at my company and my instructions for my coworkers have been ignored, too. I finally had to add a note to the front tab of the file "Please have just one instance of Excel open while running the queries" in screaming big red and yellow colors.
Unfortunately, I have never figured out what is causing this issue. If someone does know, I'd be very interested in finding it out.
