Forum Discussion
Power query output table and connections
Zack Barresse Thank you for your reply, I tried this but it wont let me overlap the table, and I don`t want to erase the table as I will loose al the related named ranges and formulas.
Zack BarresseThe query which is loaded as a table to the sheet is an appended query of two tables from the same sheet, when I make a copy of the sheet, the appended query which loads the data to the first sheet loads the data to the same table in the second sheet as well. So I duplicate all the queries and link them to the same tables on the second sheet, so now all the queries are identical and they receive their feed from their corresponding tables and sheet, I unlink the appended query table from the second sheet, so now the power query table from the second sheet is basically a table with headers. The next step is to link the second appended power query to the table on the second sheet without having to erase the table, the reason why I can not erase the table is because I have around 100 or so dynamic formulas referencing columns from this table, I have already tried to load, copy paste, erase and replace cells, as well as tried all the get data and edit connection properties options. But nothing works, the only option I see is to load the copied appended query to the second sheet and to correct all the ref errors on the formulas on the new sheet, which is a nightmare given that I need 10 copies of the same sheet in this workbook.
- I think I see what you're saying. Once a Table is unlinked it can't be re-linked without re-creating the Table, and you're hoping to preserve the dependent calculations. Unfortunately, I do not know of a way you can do this currently. You might be able to do something like this with VBA.
A possible workaround might be, in your second sheet, load the desired query to a Table adjacent to the copied Table.
Step 1.
Load the (second) query to (second) worksheet.
Step 2.
Rename the Table. You should have two Tables in the second sheet: one which is sourced to your first query, another which is sourced to your second query. Name the second table similarly to its neighboring Table. If your first Table on the second sheet is named "tTable2" then maybe name the second Table on the second sheet (the query you want to be loaded there) something like "tTable2_2".
Step 3.
Replace formula references. At this point you can go through and do a Find/Replace for all formula references from "tTable2" to "tTable2_2", assuming your columns are all still there and it won't break anything. You would also need to do this for all named ranges.
Step 4.
Remove original second Table. You should be able to now safely remove the first Table on the second sheet, the one which was a copy of your first Table with linked query.
Please note I have not tested this, but the theory sounds workable in my head, for what it's worth.