How to Allow Multiple Users to Refresh Power Query SharePoint Data in Excel
Question details
Users need a solution to allow multiple individuals to successfully refresh an Excel Power Query dataset that is connected to a file hosted on SharePoint.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user has shared an Excel workbook containing a Power Query connection to a SharePoint file, but other users are encountering credential prompts or errors when attempting to refresh the data.
- Observed behavior
- When other users click refresh, Power Query prompts them to configure web content credentials. They must authenticate correctly to access the organizational SharePoint data.
Ensure that all users attempting to refresh the data have an active Microsoft 365 organizational account and have been granted at least Read permissions to the specific SharePoint site and file.
Configure Organizational Credentials for Web Connections
Each user must authenticate their own organizational account with the correct permission scope the first time they attempt to refresh the SharePoint web connection.
When connecting to SharePoint via a generic Web connection, Power Query requires each individual user to verify their access rights. This is a one-time setup per user for that specific data source scope.
Open the Excel workbook and click on 'Refresh All' under the Data tab. Power Query will display a Web-content credentials dialog box because it requires authentication.
In the credentials dialog, select 'Organizational account' from the list of authentication methods on the left side.
From the dropdown menu, select the permission scope that matches the exact file location (e.g., choose the specific SharePoint library rather than just the root domain) to ensure proper access.
Click the 'Sign in' button, enter your Microsoft 365 credentials, and then click 'Connect'. The data will now refresh successfully.
Use the SharePoint Folder Connector Instead of Web Connection
Switching the data source from a generic Web connection to a SharePoint Folder connector provides a more stable authentication experience for multiple users.
A Lightweight Alternative for Seamless Spreadsheet Management
Dealing with complex Power Query configurations and SharePoint permission scopes in Microsoft Excel can be time-consuming. If you are looking for a straightforward, highly compatible, and resource-friendly productivity suite, WPS Office provides excellent spreadsheet tools designed for effortless collaboration and daily data management.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install WPS Office: Run the downloaded installer and follow the simple on-screen instructions to complete the setup.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel files instantly with full format retention.
Frequently Asked Questions
Why do users keep getting credential errors when refreshing Power Query from SharePoint?
This usually occurs if the permission scope is set incorrectly during the initial login. Users must select the specific SharePoint library or site level from the dropdown menu in the credentials dialog, rather than the root domain, and ensure they are using their Organizational account.
Do users have to sign in every time they refresh the shared Excel file?
No. Once a user successfully authenticates with their organizational credentials for a specific SharePoint site or file scope, Power Query saves those credentials. They will only be prompted again if their password expires or permissions change.
Why can't I find the SharePoint File connector in my Excel Get Data menu?
The specific SharePoint File connector is typically only available in certain enterprise editions of Microsoft 365. If it is missing, you can use the 'SharePoint Folder' connector as an alternative, which is more widely available.
How do I clear incorrect Power Query credentials if someone signed in with the wrong account?
In Excel, navigate to the Data tab, click on 'Get Data', and select 'Data Source Settings'. Locate the SharePoint URL in the list, click 'Clear Permissions', and then attempt to refresh the data again to trigger a fresh sign-in prompt.
