Data Protection Issues

How to Protect Confidential Data in Power Query Excel Files Shared via OneDrive

Emma BrownEmma Brown Nov 11, 2026 871 views

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.

How to Protect Confidential Data in Power Query Excel Files Shared on OneDrive
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 you start

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.

Solution 1Recommended

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.

1
Load data to a new sheet

Open your Excel workbook and allow the Power Query to load the sanitized, shareable data into a new worksheet.

2
Copy as values

Select the loaded data range, copy it, and paste it as 'Values Only' into a completely new workbook.

3
Remove queries and connections

In the new workbook, ensure no Power Query connections are present by checking 'Data' > 'Queries & Connections'.

4
Share the sanitized file

Save this new, static workbook to OneDrive and share it. This guarantees no underlying metadata or cached results from the confidential source are included.

Sanitize Output and Remove Underlying Connections
Verification Step: Always test the final shared file by opening it in an incognito window with a non-privileged account.
Free Microsoft Office alternative

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. 1. Download and install: Get WPS Office for free from the official website and install it on your device.
  2. 2. Open your spreadsheet: Open your Excel files directly in WPS Spreadsheet without losing formatting.
  3. 3. Encrypt for sharing: Use the 'Encrypt' feature under the 'Review' tab to password-protect your file before sharing.
Fully compatible with Microsoft Excel formats (.xlsx, .xls).Document encryption and advanced permission settings to protect sensitive data.Lightweight architecture without hidden background query caches.Clean and intuitive interface for seamless data transition.
microsoft office alternative - wps office

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.