I have several sheets containing ID's for different attributes (same ID can be repeated across different sheets). To make one sheet combinding these I merge them with unique and vstack into a seperate sheet (ID-sheet). And within this same file I would like to connect to the ID-sheet (as it would make downstream operations much easier). However, a connection imply power query, and one can't query files that are open. Is there a workaround to this?
I believe there is a simple solution for you: in Excel, you can query a Table in the same document.
The DAX uses:
Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content]
Click Get Data -> Other Sources -> From Table
That will pull in the existing table. You can either (1) pull across all of them in one query (if you play with the code), and do your merge there or you can (2) put each table into a separate query and then combine the queries.
Someone with more experience than me will need to comment on whether one way is faster or more effective. I expect it's dependent upon your data and layout.