Power query output table and connections

Question

HiI have a question related to output table connections in power query. I am designing a workbook where I need to copy the same sheet repeatedly in the same workbook, each sheet has two queries linked to two separate tables from the sheet, these queries are merged to a new query and then output as structured data in a output table in the same sheet. When I make a copy of the sheet, the output table in the second sheet is linked to the query from the first sheet, I need to break the connection link and link the output table to a new query copied from the currently linked query. I have tried to edit the Connection properties but all the options are greyed out as well as the Get Data from Microsoft Query. I don`t want to create a new output table for each sheet as it would mean that I have to change multiple formulas and named ranges. I would appreciate any help available I have not been able to find anything on this matter online. Thanks

zack barresse · Answer

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.

zack barresse · Answer

Remove those linked query loads. Open your queries &amp; connections task pane, right-click the desired query, load to..., select where it should go. If you've already linked it somewhere else, like a PivotTable, it will break that connection.

_parjam_ · Answer

Zack Barresse&nbsp;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 barresse · Answer

I don't understand. If it's a copy of the original sheet with queries loaded to Tables, remove the Tables and start with your new queries. Any old named ranges associated with your old Table will still be preserved.

_parjam_ · Answer

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.

Forum Discussion

Power query output table and connections

5 Replies

Resources