Dumb question and I am not sure I am asking properly. Is it possible to use a table or named range within the PQ editor without making it a separate query?
For example, say I wanted to append or merge a table or named range within the worksheet to an existing query, is this possible or do I need to load the table or named range into PQ editor as well?
Unless I'm totally mistaken, yes you need to connect PQ to all tables/ranges you want to use. It creates a query, but that doesn't really matter. Or why would you not want to connect?
I have a lot of queries within the PQ editor (over 100). I was just looking to simplify a little bit. It is a nightmare at times working with all the queries. But, there is a lot of separate data that is being utilized. It is mostly connections but still, just looking to downsize the number of queries and I thought maybe this was one method.
100 queries seem a lot.
Perhaps it will help if you group the queries in the Queries & Connections window. Use some logic that enables you to find particular types of queries when you need to review or edit them. And collapse groups the contain intermediate (staging) queries that you probably never will need to edit. And use clear and descriptive names for the groups and queries. Perhaps you have already done so. Then I don't really know anything else you could do.
I do have them in groups. It involves 20 - 23 separate weeks and each week has about 6-7 sets of different queries. I got to thinking this morning, since the data is similar, I could combine some of the tables into one large table and then filter within the query. That would cut down on some of the queries.
Speaking of collapsing the groups, is there a way (setting) that when you enter the PQ editor, all groups are automatically collapsed?
That I'm not aware of. Sorry.
ExcelNovice2020, I think you're probably dealing with a bigger issue than whether or not this is possible, and based on the conversation so far it does seem like having 100 queries means there's probably a better way.
However, I did want to mention that you probably can (haven't tested) reference a named range or table without creating another query. The way I would envision doing that is by creating a query to reference all objects in the current Excel workbook, then finding the named ranges, and then expanding those named ranges (assuming they're all formatted the same) into a single location.
In other words, you'd have a custom column that would expand the data within a cell based on a name in another column (I.E. Excel.CurrentWorkbook... [Other Column]...) and it could be contained in one place.
That said, it's also not the most simple solution, but it could accomplish what you're looking for.
