Fix Excel Power Query SharePoint User-Specific Path Error
Question details
Users need to resolve a data source error in Excel Power Query that occurs when coworkers try to refresh a connection pointing to a user-specific local SharePoint or OneDrive sync path.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user creates a Power Query connection to a synchronized SharePoint or OneDrive file, but when coworkers attempt to refresh the data, it fails because the file path contains the original creator's Windows username.
- Observed behavior
- The Power Query connection works perfectly for the creator but returns a DataSource.Error for coworkers due to the hardcoded, user-specific local directory path.
Ensure you have the exact SharePoint or OneDrive for Business web URL for your data source file, rather than a local folder path, and verify you have the correct organizational credentials to access it.
Change to a Web-Based SharePoint or OneDrive Connection
Replace the local user-specific synced folder path with a universal SharePoint or OneDrive web URL so all authorized coworkers can successfully refresh the query.
When connecting to files via a locally synced OneDrive or SharePoint folder, Excel records the exact file path including your specific Windows username (e.g., C:\Users\YourName\...). To ensure coworkers can refresh the query, the data source must point to the cloud-based web URL instead of the local hard drive.
Open the source file in Excel Online or locate it in your SharePoint/OneDrive web portal. Copy the URL from the browser's address bar. Delete any parameters at the end of the URL, such as '?web=1'.
Open your main Excel workbook, go to the 'Data' tab, and click 'Get Data' > 'Data Source Settings'.
Select the problematic local file path, click 'Change Source', and paste the copied SharePoint or OneDrive web URL into the file path box. Click 'OK'.
If prompted for credentials, select 'Organizational account' from the left sidebar, click 'Sign In' using your Microsoft 365 account, and then click 'Connect'.
Automate Data Refreshes Using Power Automate
Bypass manual user refreshes and local path conflicts entirely by setting up a centralized, scheduled refresh through Power Automate.
Try WPS Office for Seamless Spreadsheet Collaboration
Advanced data modeling tools like Power Query can sometimes lead to complex pathing and credential errors in Microsoft Excel. If you need a reliable, lightweight, and completely free alternative for your daily spreadsheet and collaboration tasks, WPS Office is an excellent choice. It offers powerful data analysis features, built-in cloud sharing, and full compatibility with standard Excel files.
- 1. Download and Install: Get the free WPS Office suite from the official website and install it on your device.
- 2. Open Your Spreadsheets: Directly open your existing .xlsx files with WPS Spreadsheet without worrying about formatting loss.
- 3. Collaborate in the Cloud: Use WPS Cloud to share document links with coworkers, avoiding local file path conflicts completely.
Frequently Asked Questions
Why does Power Query show a DataSource.Error for my coworkers?
This usually happens when the data source path points to a local directory (like a synchronized OneDrive or SharePoint folder containing your specific Windows username) instead of a shared web location. Because your coworkers do not have the same username on their computers, Excel cannot find the file path when they try to refresh.
How do I find the correct web URL for a SharePoint Excel file?
Open the file in your web browser via SharePoint or OneDrive. Copy the URL directly from the browser's address bar. Before pasting it into Power Query, be sure to delete anything from the question mark onward (e.g., '?web=1') to ensure it links directly to the file.
Can I use system variables like %USERPROFILE% in Power Query to make the path dynamic?
Power Query does not natively resolve Windows environment variables like %USERPROFILE% directly in the Source step. While you can achieve this with complex custom M code or parameter tables, switching to the universal SharePoint web URL is generally the easiest and most robust solution.
