How to Protect Confidential Data in Power Query Excel Files Shared via OneDrive
Question details
The user wants to know if sharing a public Excel workbook that uses Power Query to import data from a confidential workbook exposes the source data, and how to protect it.

- Product
- Microsoft Excel / OneDrive
- Device & OS
- not provided
- Scenario
- Sharing an Excel workbook containing Power Query connections to confidential source files via OneDrive.
- Observed behavior
- The user needs to prevent unauthorized access to sensitive query definitions, cached results, or connection metadata within the shared public workbook.
Before sharing any workbook linked to external data, ensure you have a backup of both your public and confidential source files. It is critical to test sharing links with an account that has absolutely no permissions to the source file to verify what data is actually exposed.
Sanitize Output and Remove Underlying Connections
The most secure way to share queried data is to break the connection to the confidential source before sharing the file.
A view-only OneDrive link does not guarantee protection. Excel workbooks store query definitions, cached results, and connection strings in their metadata (like customXML within the file's ZIP container). To prevent exposure, never share the live query file with external users.
Open your Excel workbook and allow the Power Query to load the sanitized, shareable data into a new worksheet.
Select the loaded data range, copy it, and paste it as 'Values Only' into a completely new workbook.
In the new workbook, ensure no Power Query connections are present by checking 'Data' > 'Queries & Connections'.
Save this new, static workbook to OneDrive and share it. This guarantees no underlying metadata or cached results from the confidential source are included.

Restrict Source Permissions and Clear Cache
If you must keep the query intact, ensure the source file is strictly access-controlled and clear residual data caches.
Secure Your Spreadsheets with WPS Office
If you are concerned about hidden metadata, connection strings, or cached query data in Excel, consider using WPS Office. It provides robust spreadsheet management, excellent format compatibility, and lightweight performance without complex background query caches that might inadvertently expose confidential data.
- 1. Download and install: Get WPS Office for free from the official website and install it on your device.
- 2. Open your spreadsheet: Open your Excel files directly in WPS Spreadsheet without losing formatting.
- 3. Encrypt for sharing: Use the 'Encrypt' feature under the 'Review' tab to password-protect your file before sharing.

Frequently Asked Questions
Does a view-only link in OneDrive prevent users from downloading the Excel file?
Not inherently. While a view-only link restricts editing, users may still be able to download the file and inspect its contents, including hidden sheets, query connections, and metadata, unless 'Block download' is specifically checked when creating the sharing link.
Can someone extract my Power Query connection string from a shared Excel file?
Yes. Even if the data is protected, connection strings and query definitions (M code) are stored in the workbook's underlying XML structure. If a user downloads the file and changes the extension to .zip, they can inspect the customXML files and potentially view this information.
How can I safely share only the results of a Power Query?
The safest method is to copy the queried data and paste it as 'Values' into a brand new, disconnected workbook. This ensures no background queries, cached results, or connection metadata are transferred to the shared file.




