CODEWITHSUNDEEP
excelpowerquery
Rich Ard
Rich Ard

Reputation: 191

Can Power Query output load to an existing Excel table?

I inherited a monstrous Excel spreadsheet that had more VLOOKUP and range references than I could have imagined were possible, and analysts were tired of copying and pasting into it. Power Query to the rescue!

My question was:

Can Power Query load data into an existing table?

Power Query loads data into a new table upon creation of a query, and this would break a lot of the downstream connections that I don't have the bandwidth to recreate. I was only able to find a solution to this issue in a Reddit thread; sharing it here for posterity.

Upvotes: 1

Views: 5270

Answers (1)

Rich Ard
Rich Ard

Reputation: 191

Assuming all references to the Table are in the same workbook,

  • Move your named table (in this example, mytable into a new workbook (sometimes the sheet and all is easiest),
  • Copy and paste the PQ query into the new workbook and delete it from the original workbook.
  • Save and close the new workbook.
  • Save and close the original workbook. It will now contain references to the table in the new workbook.
  • Open the new workbook, and rename mytable to OLDmytable.
  • Open the PQ query and Load-to a new Table, using the original name mytable. Ensure that ALL the columns are the exact same name as the original table!
    • The columns don't need to be ordered the same unless they are used by VLOOKUP (your next task is to replace VLOOKUP with INDEX/MATCH).

Now, in your new workbook, there is a power query loaded/owned table with the same name as the original: mytable.

Your original workbook has references to this new workbook and the mytable - it doesn't know that this is actually a different table (now being filled by PQ) to the original.

You should now open the original workbook and PROVE that all references to the original Table now point to this new Table.

  • If there are #REF errors - you have got some column names wrong! Close the original again without saving and fix in the new workbook/query

  • Once everything appears correct, with both workbooks open, move the sheet/table back to the original workbook (the Power Query will follow it to the original workbook, and appear as connection only in the new workbook)

Upvotes: 1

Related Questions