Power Query Is Not Secure for Excel Row-Level Permissions
Question details
The user needs to know whether Power Query-filtered Excel workbooks connected to a SharePoint master workbook can enforce row-level permissions, and what secure alternatives exist.
- Product
- Microsoft Excel with Power Query
- Device & OS
- not provided
- Scenario
- The user connects separate Excel files to a SharePoint master workbook through Power Query, intending each user to see only assigned rows.
- Observed behavior
- Excel protection and Power Query filters do not create a reliable security boundary because users may still access or refresh the underlying data.
Before choosing a row-level permission method, confirm which users should see which rows and whether the underlying SharePoint workbook contains sensitive data that must not be exposed through refresh or file access.
Use Power BI row-level security instead of Power Query filters
Choose this when you need enforceable row-level permissions and still want users to analyze their allowed data in Excel.
Excel worksheet protection, workbook structure protection, and Power Query filters are not designed as user-level access control. They can reduce accidental changes but cannot prevent a determined user from accessing refreshed data if they have the connection or file access.
In Power BI Desktop or the Power BI service, connect to the SharePoint master workbook or underlying data source and load the data into a dataset.
In the Modeling tab, select Manage Roles, create a role, and add a DAX filter such as [UserEmail] = USERPRINCIPALNAME() to restrict rows.
Publish the dataset to the Power BI service, open the dataset security settings, and add the appropriate users or groups to each role.
In Excel, use Data > Get Data > From Power BI to connect with a PivotTable, so users see only the rows allowed by the Power BI role.
Keep sensitive source data in a service that enforces permissions
Use this when the source itself contains sensitive data and must be protected independently of any Excel report.
Try WPS Office as a Free Microsoft Excel Alternative for Daily Spreadsheet Work
WPS Office does not turn Power Query filters into row-level security, but it is a free, lightweight Office suite with strong Microsoft Excel format compatibility. You can open, edit, and save .xlsx workbooks in a familiar interface while using Power BI or SharePoint permissions for actual row-level security.
- 1. Download WPS Office: Go to the official WPS website and download WPS Office for Windows, macOS, Android, or iOS.
- 2. Open your Excel workbook: Launch WPS Spreadsheets, click Menu > Open, and select the existing .xlsx or .xls file.
- 3. Edit and save in Excel format: Use the familiar ribbon tools to review formulas and formatting, then save the file as .xlsx to keep Microsoft Excel compatibility.
- 4. Use a separate security layer for row-level access: Keep actual row-level restrictions in Power BI or SharePoint permissions, and use WPS Spreadsheets for compatible editing and analysis.
Frequently Asked Questions
Can Excel worksheet protection or workbook passwords enforce row-level permissions?
No. Excel protection is designed to prevent accidental changes and can often be removed, so it is not a reliable security boundary for row-level permissions.
What should I use instead of Power Query for row-level security in Excel?
Use Power BI with properly configured row-level security roles. Users can then analyze authorized data through Excel-connected PivotTables or other supported connections.
Can users access hidden or filtered rows by refreshing a Power Query connection?
Yes. Power Query filters control data transformation, not user access. A user with access to the query or underlying file may still reach the source data.
Does WPS Office provide a row-level security feature for Excel files?
WPS Office opens and edits Excel files but does not add a security layer to Excel Power Query. For actual row-level security, use Power BI or a permission-enforcing data source; WPS Office is a free alternative for everyday spreadsheet work.
