Power Query is all about creating a refreshable connection to a data source and making it easy for us to update the query results. However, sometimes we want to preserve those results without being replaced by future changes in the data source.
This is a common request in scenarios where we want to keep historical records or keep data unchangeable for auditing purposes. In such situations, after using Power Query to load data to a table in our Excel file, we want to prevent users from changing the information in that table by clicking “Refresh” or “Refresh All.”
Let’s see how we can disconnect a Power Query table from the data source.
Case Scenario: We have an Excel file with one query only. When we open the Queries and Connections pane from the Data tab, we see one query that connects to an external CSV file and then transforms the data and brings it into our Excel file loaded as a table.
Everyday, we run the query and save the current file as a new file named after that day’s date so that other users can have a record of what the data was on each day. However, we need to ensure that no one will hit “refresh all” and by accident changes the data that is available in the dated file. It is very much like capturing a picture of today’s data on a separate file and having it there in a static form.
Solution: There is a way to disconnect the table from the query. We will see how to do that manually and then we will use VBA to automate the repetitive work for us.
To watch my YouTube video on this topic, click here.
The manual process to disconnect the query table from the query goes like this:
Select any cell in the Excel table generated by the query and then:
- Right click and from the drop-down list, select “Table.”
- Select “Unlink from Data Source.”
A pop-up appears with the message “This will permanently remove the query definition from the sheet. “
Click “OK.”
The Queries and Connections pane now shows “Connection only” for the query that had generated the table. Therefore, the query did not disappear and can be edited. All the steps are still available, as is the data source path.
If the user’s credentials allow him to access to the source files, and the query can be edited, the user will have access to the original data, which could be a concern in situations where there may be some confidential information stored in the source that is not visible in the final query table.
If we try to refresh the data from the table, we see that the refresh option is not available (greyed out).
However, from the query, we can do (1) “Load to” and then choose (2) “Table” and pick a destination cell (3).
This way, we will load the data to the file again, into a new table.
Unlinking the table from the data source is not the same thing as changing the loading destination to “Connection only”. If you try to take a query that is loaded to the workbook as a Table and change its loading destination to “Connection only”, a pop up appears “This query is currently connected to a table in your workbook. Loading the query as a connection only will delete the table. Do you want to continue?”
If you say “yes”, the table disappears, and the query remains as “Connection Only.”
When you unlink the query table from the query, both the table and the connection remain in the file, but the table is no longer “refreshable” by the query.
Automating with VBA:
Although disconnecting the table from the query requires a few clicks only, having to do that manually every day plus having to save a separate copy of the file is worth taking the time to automate and transform all these steps into a one-click-only process. To do that, do as follows:
Save the file that contains the query as an Excel Macro-Enabled Workbook (* .xlsm) using the Save As and picking the type from the drop-down in the save window.
Let’s start the macro in the VBA editor by going to the developer tab and opening Visual Basic. Click “Insert” and choose “module” from the drop-down list. In that module, let’s start a new procedure by typing “Sub UnlinkTable” and then press Enter.
Let’s start by typing the list of steps we want to automate. Each step is written as a comment in the code by placing a single quote character before the text – comments are not perceived by VBA as an instruction to run.
The steps are:
‘Update the data.
‘Save the current file.
‘Save the file as workbook without macros.
‘Unlink the table in the new file
‘Save the unlinked file
You can find the complete code for this macro at the end of this blog post. The details of each code section are explained below.
The first three actions will be performed in the current workbook and then the final two actions will be performed after we save the current file as a new workbook.
The code will start:
With ThisWorkbook (meaning the one where we are writing the code)
After the first three steps, we enter “End With”
After With ThisWorkbook() entry, we update the data including all the queries and pivot tables in the workbook by adding .RefreshAll
If you just want to refresh a particular query only, then the code would have to be different.
Next, we want to save the current file using the code .Save to keep the initial file with all its content.