How to Share Power Query Data Without Exposing Queries or Sources
Question details
The user needs to share a workbook connected to SQL Server via Power Query so others can refresh filtered data, but without allowing recipients to see the underlying query structure or database connection details.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Sharing dynamic reports with external or internal users who need the ability to refresh the data themselves without gaining unauthorized visibility into backend database architectures.
- Observed behavior
- By default, Excel Power Query does not prevent workbook recipients from opening or modifying the query. Users with database access may also be able to view the raw source data, creating a security and privacy risk.
Ensure you have the necessary permissions to set up intermediate data sources, such as a designated SharePoint site, OneDrive folder, or a Power BI Pro license, before attempting to secure your queries.
Use an Intermediate Workbook on SharePoint or OneDrive
Extract the data to a master file stored securely in the cloud, and provide users with a secondary workbook that queries the master file rather than the SQL database.
Because Power Query in Excel cannot hide connection strings or query steps from users, you must decouple the final report from the SQL database. An intermediate file acts as a secure buffer.
Open a new Excel workbook, connect it to your SQL Server via Power Query, and load the required data. Save this workbook to a secure SharePoint or OneDrive location.
Configure the sharing settings on the Master Workbook in SharePoint/OneDrive so that only authorized administrators can view or edit it.
Open a second, blank Excel workbook. Go to the Data tab, select 'Get Data', and connect to the Master Workbook stored on SharePoint/OneDrive (using the Web or Excel Workbook connector).
Distribute this second workbook to your end users. When they click 'Refresh All', it will pull data from the intermediate file without exposing the original SQL Server connection details or query steps.
Deploy Data via Power BI
Publish your dataset to the Power BI Service, where you can enforce Row-Level Security (RLS) and strict viewer permissions.
Try WPS Office for Secure and Lightweight Data Management
Dealing with advanced Power Query security limitations in Microsoft Excel can be complex. For everyday data analysis, reporting, and secure document sharing without the heavy overhead, WPS Office provides a lightweight, highly compatible alternative. It supports robust file encryption and seamless spreadsheet handling.
- 1. Download and Install: Visit the official WPS Office website and download the free installer for your operating system.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheets and seamlessly open your existing .xlsx or .csv files without losing formatting.
- 3. Secure Your Files: Use the built-in encryption features under the Menu > File > Encrypt option to securely password-protect your data before sharing.
Frequently Asked Questions
Can I password protect just the Power Query connection in Excel?
No, Excel does not currently offer a native feature to password-protect or hide individual Power Query connections or query steps from users who have access to the workbook.
Will hiding the worksheet protect my underlying SQL data?
Hiding the worksheet containing the loaded data does not protect the query itself. Users can easily unhide the sheet or open the Power Query Editor to view the source details and connection strings.
Does saving the Excel file as static values solve the security issue?
Yes, if the recipient does not need to refresh the data themselves, you can copy and paste the query results as static values or export the report to PDF. However, this removes the dynamic refresh functionality for the end user.
