Apr 21 2020 11:49 AM
Hi
I 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
Apr 21 2020 05:57 PM
Apr 22 2020 10:35 AM
@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.
Apr 22 2020 01:23 PM
Apr 23 2020 05:20 AM
@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.
Apr 23 2020 10:01 AM