Power Query Problems

How to Protect Power Query Queries and Sensitive Source Data in Excel

Aamir Naveed Akram Oct 23, 2026 869 views

Question details

The user needs a way to prevent unauthorized access to sensitive source data queried via Power Query, as standard Excel worksheet protection does not prevent queries from being copied.

How to Protect Power Query Queries and Sensitive Source Data in Excel

Product: Microsoft Excel, SharePoint
Device & OS: not provided
Scenario: Sharing an Excel workbook containing Power Query connections without exposing sensitive source data to unauthorized users.
Observed behavior: Users can bypass worksheet protection by copying Power Query queries into a new workbook, granting them unauthorized access to sensitive data from SharePoint source files.

Before you start

Before securing your data, map out exactly where your source Excel files are stored (e.g., SharePoint Online) and review the current access permissions for those locations.

Solution 1Recommended

Restrict Access to Source Files in SharePoint

The most secure way to protect Power Query data is to restrict permissions at the source file level in SharePoint, ensuring unauthorized users cannot refresh or retrieve data even if they copy the query.

Excel's built-in worksheet protection only locks the front-end user interface. It does not encrypt or protect the underlying Power Query (M code) definitions. If a user has read access to the source files, they can simply copy the query to a new workbook.

By managing permissions directly in SharePoint, you enforce data security at the source. If someone copies the query into another workbook, the SharePoint authentication process will block them from accessing or refreshing the sensitive data.

Navigate to the Source Files

Log in to your SharePoint Online account and navigate to the document library or folder containing your source Excel workbooks.

Open the Manage Access Panel

Select the source workbook containing the sensitive data, click the three vertical dots (ellipsis) next to the file name, and choose 'Manage Access' from the context menu.

Remove Unauthorized Users

Review the list of users and groups in the Manage Access panel. Click the drop-down next to any user who should not have access to the source data and select 'Stop sharing' or change their permission to 'Can view' if you only want to restrict edits. To completely block Power Query access, remove their permissions entirely.

Verify Permissions Across All Sources

Repeat this permission modification process for every Excel workbook in SharePoint that acts as source data for your Power Query reports.

Restrict Access to Source Files in SharePoint

Authentication Enforced: When users without permission attempt to refresh the copied query in a new workbook, SharePoint will prompt them for credentials and subsequently deny access, keeping your source data secure.

Free Microsoft Office alternative

Try WPS Office for Secure and Efficient Spreadsheet Management

If you are looking for a lightweight, cost-effective alternative to Microsoft Office, WPS Office offers powerful spreadsheet capabilities with seamless document encryption, helping you keep your local data secure without complex configurations.

1. Download the Software: Visit the official WPS Office website and click the download button for your operating system.
2. Install WPS Office: Run the installer and follow the on-screen prompts to complete the setup process.
3. Encrypt Your Spreadsheets: Open your Excel files in WPS Spreadsheets, navigate to the 'Menu', select 'File Encryption', and set a secure password to protect your data.

Highly compatible with Microsoft Excel (.xlsx, .xls, .csv) formats.Built-in document encryption to password-protect sensitive spreadsheets natively.Free, lightweight, and fast alternative to Microsoft Office.Familiar user interface ensuring a seamless migration with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can I password-protect the Power Query editor in Excel?

No, Excel does not currently offer a built-in feature to password-protect the Power Query editor or specific query definitions. Security must be handled by restricting access to the source data files.

Does locking an Excel worksheet prevent Power Query from refreshing?

Locking a worksheet prevents users from changing the layout or modifying specific cells directly, but it does not stop background queries from refreshing if the user has network or SharePoint access to the source data.

Why can users still see my data after I protected the workbook?

Workbook and worksheet protection only restrict changes to the workbook's visual structure and cell contents. They do not encrypt the data connections, meaning users can copy the queries into a new, unprotected workbook to view the data if source permissions are not properly restricted.

Excel & Data Problems