How to Change an Excel Query from a Local Path to SharePoint
Question details
The user needs to change the data source of an existing Excel Power Query from a user-specific local or OneDrive path to a direct SharePoint URL.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Users are collaborating on shared workbooks, but Power Queries fail to refresh for other team members because the query relies on a localized, synced folder path instead of the centralized cloud location.
- Observed behavior
- The query fails to update on other computers, displaying a path error, because it is referencing a local C: drive directory tied to the original creator rather than directly pointing to the shared SharePoint environment.
Ensure you have the necessary Read permissions for the target workbook on SharePoint and that you are signed into Excel with your organizational Microsoft 365 account.
Connect Directly to the SharePoint Workbook Link
By replacing the local file path with the direct SharePoint URL, the query will use web credentials and remain accessible to anyone with the correct permissions.
When a SharePoint document library is synced to your computer via OneDrive, Excel often defaults to using the local path for queries. Updating the source to the direct web link resolves sync dependency issues and ensures seamless collaboration.
Open your document library in SharePoint, select the source workbook, click the three-dot menu, choose 'Details', and copy the direct link or path to the file.
In your destination Excel workbook, navigate to the 'Data' tab on the ribbon, click 'Get Data', and select 'Data Source Settings'.
Select the current local path connection from the list, click 'Change Source', and paste the copied SharePoint link into the URL or File Path box.
Click 'OK'. If prompted for credentials, select 'Organizational account' and sign in. Close the settings window and click 'Refresh All' to load the data from SharePoint.
Try WPS Office for Seamless Data Management
If complex Power Query dependencies and SharePoint syncing errors are slowing you down, consider WPS Office. It provides a lightweight, highly compatible alternative for everyday spreadsheet tasks, data analysis, and team collaboration.
- 1. Download and Install: Get the free version of WPS Office from the official website and install it on your device.
- 2. Open WPS Spreadsheets: Launch the application and select 'Spreadsheets' to start managing your data.
- 3. Work with Excel Files: Open your existing .xlsx workbooks directly; all your formatting and data will be perfectly preserved.
Frequently Asked Questions
Why does my Excel query show a local path instead of SharePoint?
If you use the OneDrive or SharePoint sync client to access files via File Explorer, Excel automatically registers the local C: drive path when you select a file. To fix this, you must manually copy the file's web URL from SharePoint and use it as the data source.
What permissions do I need to refresh a SharePoint query?
You must have at least Read access to the specific SharePoint document library where the source file resides, and you must be signed into Excel using the Microsoft account associated with your organization.
Can I parameterize the SharePoint path in Power Query?
Yes. You can create a parameter in Power Query to store the base SharePoint URL. This allows you to easily update the file path in one central location if the SharePoint site or library changes in the future.
Why do I get an authentication error when pasting the SharePoint link?
This typically occurs if Power Query defaults to 'Anonymous' or 'Windows' credentials for a SharePoint Online link. Go to Data Source Settings, click 'Edit Permissions', and ensure you select the 'Organizational account' option to sign in.
