ScrubDrop › Guides

Power Query in an Excel workbook: the source paths, server names and steps a query keeps after the data is loaded

Last updated: 28 October 2026

This page is about the queries themselves. The connection that ties a query to the sheet, and other full paths in a workbook, are in full paths in Excel workbooks; the copy of the source data a pivot table keeps is in pivot caches; and custom XML parts in general are in custom XML and embedded objects.

A query is a list of recorded steps

Microsoft's page About Power Query in Excel says: "The Power Query Editor keeps track of everything you do with the data by recording and labelling each transformation, or step, that you apply to the data." It adds: "Each transformation is recorded as a step in the background. You can even modify and write your own steps using the Power Query M Language in the Advanced Editor." When you refresh, "each step runs automatically". Its page Manage queries (Power Query) says that "When you create a query, connection information is created behind the scenes."

So the steps stay in the workbook for as long as the query does, including steps that took something out. The loaded table shows the result; the query shows how it was made and where from.

What a query's steps can name

The steps are formulas in the Power Query M language. Microsoft's M function reference shows what the common ones take as arguments:

• A file's path. File.Contents takes path as text and "Returns the contents of the file, path, as binary". A query built on a file names it by its path, so a query built on a workbook or CSV in your user folder, or on a server and share, can say where that is.
• A server, a database and a SQL statement. Sql.Database takes server as text and database as text, and an option Query, "A native SQL query used to retrieve data". The statement can name tables and columns the recipient never sees.
• The columns you removed. Table.RemoveColumns "Removes the specified columns from the table provided", so the step that removes a column names it. The loaded table does not have the column; the query still has its name.
• The values you filtered on. Table.SelectRows keeps its condition in the step (the reference's example is each [CustomerID] > 2), so a customer number or a name you filtered out is written there.

Microsoft's page Manage data source settings and permissions describes where data source credentials are managed (Data › Get Data › Data Source Settings, for "Data sources in current workbook" or "Global permissions"); it does not say where they are stored. [MS-QDEFF], described below, names no part for credentials, but anything typed into a step, such as a server name or a key in an address, is part of the formula.

Where the query is in the .xlsx

Microsoft's specification [MS-QDEFF]: Query Definition File Format describes "a structure that is used to store the Power Query Formulas of the queries in a spreadsheet and their associated metadata". Its overview says: "It is stored within a CustomXML Part in the spreadsheet file."

The container is base64 text: once decoded, it holds the package with the formulas and the other parts, one after another (2.2 Top-level Binary Stream), and that package is an Open Packaging Conventions package, the same kind of ZIP package as the .xlsx itself (2.3 Package Parts). That is why searching the unzipped .xlsx for a path or a server name can find nothing even when a query names it. A refresh error message, a sheet name or a column name can be kept in the details of a query the same way.

The query also has a connection, in xl/connections.xml, and a query loaded to a sheet fills a table there; both are described in full paths in Excel workbooks. A query can also be loaded to the Data Model (the metadata entry AddedToDataModel, "Whether the query has been loaded to the spreadsheet data model"); this page does not cover the Data Model.

Who can see it

Anyone who opens the workbook in a version of Excel with Power Query. On Windows, Data › Queries & Connections lists the queries on its Queries tab; Edit opens a query in the Power Query Editor, where each step is listed and the Advanced Editor shows the formula (the Manage queries page lists Advanced Editor among the commands of the Queries pane in the editor). Microsoft's page Import and shape data in Excel for Mac (Power Query) describes the Power Query Editor there too, for Microsoft 365 subscribers "running Version 16.69 (23010700) or later" (another section of the same page still says "authoring in the Power Query Editor is not available in Excel for Mac"), and the Manage queries page says Excel for the web shows "all queries in the current workbook along with query details, such as row count, last refresh date, location, and load status."

Opening the workbook somewhere that cannot reach the source does not hide the query: the steps are in the file, and the recipient sees them whether or not a refresh works.

What Document Inspector covers

Microsoft's page Remove hidden data and personal information by inspecting documents, presentations, or workbooks lists, for Excel, "Inactive external data connections" among what the Document Properties and Personal Information inspector removes, and a Custom XML Data inspector that removes "Custom XML data that might be stored within a workbook". The page does not mention Power Query or queries, so it does not say whether either inspector removes a query's container. Check a copy afterwards, as below.

Read and remove queries in Excel

1. Work on a copy. Open Data › Queries & Connections and look at the Queries tab: every query of the workbook is listed there, including queries loaded only as a connection.
2. For each query, select Edit and read its steps in the Power Query Editor, then open the Advanced Editor and read the whole formula: file paths, server and database names, SQL statements, the columns removed and the filters.
3. Open Data › Get Data › Data Source Settings with "Data sources in current workbook" selected, to see every source the queries use.
4. If the recipient does not need to refresh the data, delete the queries: right-click each one on the Queries tab and select Delete, which the Manage queries page describes as "Removes a query." That page does not say what happens to the table the query loaded, or to the query's connection. For connections, Microsoft's page Create, edit, and manage connections to external data says that removing one does not remove any object or data from the workbook. Check the sheets, and the Connections tab of Data › Queries & Connections, afterwards. Another way is to copy the loaded tables and paste them as values into a new workbook.
5. If the recipient does need to refresh, take out the steps that say more than they need, and point the sources at a location they can use, with Change source in Data Source Settings.
6. Save, close and check the copy you are about to send, as below.

Check a copy of the file

1. Make a copy of the .xlsx, rename it to .zip and unzip it.
2. If there is a customXml folder, open each item file. One whose root element is DataMashup holds the workbook's queries. If none is, the file has no Power Query container in this form.
3. To read the formulas, decode the base64 text of that element with a base64 tool. The result starts with four bytes for the version and four for the length of the package (2.2 above), least significant byte first; the package follows, and other parts come after it. Save just that many bytes, from the ninth byte on, as a .zip, and open Formulas/Section1.m. Decoding Microsoft's example gives the length bytes 45 03 00 00, that is 837, and a package that starts at the ninth byte with the letters PK, as ZIP files do.
4. Open xl/connections.xml, if there is one, for the connections, and the xl/queryTables folder, if there is one, for the tables they fill.
5. Finding nothing this way shows only that these searches found nothing, not that the file holds nothing else.

What ScrubDrop does

ScrubDrop reads the .xlsx in your browser; it is not uploaded. For free, one file at a time, it lists the query container as a custom XML part, by its file name only (for example customXml/item1.xml; with several custom XML parts, open each as above to see which one is DataMashup), and the workbook's connections under links to other files, among the other kinds of hidden data it supports. It does not decode the container, so it does not list the query names, paths or servers inside it: read those in Excel as above. The free version removes the document properties and comments and accepts tracked changes; the queries and connections stay.

The [MS-QDEFF] pages we read do not say in which text encoding the container part is written. ScrubDrop reads custom XML parts as UTF-8, and we checked what this section says only with synthetic workbooks whose container is UTF-8. A part in another encoding, such as UTF-16, makes ScrubDrop stop with an error for that file: nothing is listed and no file is offered. Read and delete the queries in Excel as above. A workbook's Data Model was not tested for this page; parts ScrubDrop does not keep are listed as such and are not written to the new file (see the limits).

ScrubDrop Pro removes custom XML parts, and the query container goes with them; it removes links to other files, and the connections go with them. The two are separate choices. A query loaded to a table on a sheet fills that table from its connection, and removing links to other files is then refused, with the sentence "A table in this workbook is filled from a data connection being removed. Convert it to a range in Excel first, or untick links to other files." With custom XML chosen and links to other files unticked, the query container goes and the connection and the table stay, so the connection then points at a query that is no longer there: delete the queries in Excel instead, or convert the tables to ranges first and then choose both. A query loaded only as a connection goes with both chosen. The values in the cells stay. The result is read again with separate parsers, and a file that does not pass is not offered. Macro-enabled workbooks (.xlsm) are not opened: save them as .xlsx first.

Questions

Sources: the Microsoft pages linked on this page were checked on 28 October 2026. ScrubDrop is not affiliated with Microsoft.