Connect Excel Power Query to a SharePoint Folder Instead of a Local Path
Question details
Change a Power Query report that imports files from one employee's local Windows folder so multiple authorized team members can refresh it from SharePoint.
- Product
- Microsoft Excel Power Query
- Device & OS
- Windows
- Scenario
- A team needs to use and refresh a shared report without editing a source path such as C:\Users\Username\Documents.
- Observed behavior
- The current Folder source is tied to one user's local Windows path, preventing other users from reliably refreshing the report.
Move the source files to the SharePoint document library and confirm that every report user has permission to view the site and files. Copy the SharePoint site URL, not a synced OneDrive folder path or an individual file URL.
Replace the local Folder source with the SharePoint Folder connector
Use this method when the report combines files stored in a shared SharePoint document library.
The SharePoint Folder connector reads files from a SharePoint site rather than from a Windows user profile. After connecting, filter the Folder Path column to limit the query to the required library or subfolder.
In Excel, select Data > Queries & Connections, then right-click the existing query and choose Edit.
In the Power Query Editor, select Home > New Source > SharePoint Folder. In Excel's Get Data menu, the same connector is available from Data > Get Data > From File > From SharePoint Folder.
Enter the site address, such as https://contoso.sharepoint.com/sites/Finance, and select OK. Do not enter a local synced path such as C:\Users\Username or a URL that points directly to one file.
When prompted, select Organizational account, sign in with the account that has access to the site, and choose Connect.
In the file list, filter the Folder Path column to the target document library or subfolder. Then use Combine Files or update the existing transformation steps to process the filtered files.
Select Home > Close & Load, then have another authorized team member select Data > Refresh All to verify that the shared source refreshes correctly.
Store the SharePoint site and folder filter in Power Query parameters
Use parameters when the SharePoint location may change or when you want the query location maintained in one central place.
Use WPS Office for Everyday Shared Spreadsheet Work
Microsoft Power Query's SharePoint Folder connector is managed within Excel. For everyday spreadsheet editing, sharing, and format-compatible collaboration, WPS Office is a free, lightweight alternative with a familiar spreadsheet interface that makes moving from Microsoft Office straightforward.
Frequently Asked Questions
What URL should I enter in the SharePoint Folder connector?
Enter the SharePoint site URL, such as https://contoso.sharepoint.com/sites/Finance. Do not use a local synced folder path, a OneDrive path, or the URL of an individual workbook.
How do I limit a SharePoint Folder query to one subfolder?
After Power Query returns the site file list, filter the Folder Path column to the required document library or subfolder. You can replace the fixed filter value with a Text parameter for easier maintenance.
Why can one employee refresh the SharePoint query but another cannot?
Each person refreshes using their own organizational account. Check that the person has at least read access to the SharePoint site, document library, and source files, then use Data > Get Data > Data Source Settings to clear or update credentials if necessary.
Can I change an existing local Folder query without rebuilding all transformations?
Usually, yes. Create a SharePoint Folder source, then update the query's Source step and any early navigation or folder-filter steps. Review later steps to ensure their expected columns and file structure still match the SharePoint results.
Can WPS Office create or refresh Excel Power Query SharePoint Folder connections?
Power Query SharePoint Folder connections are an Excel Power Query feature. Use Excel to configure and refresh that connector; WPS Office can be used for compatible .xlsx, .xls, and .csv spreadsheet work outside that Power Query workflow.
