How to Fix Power Query Cannot Retrieve SharePoint Files with Hash Characters
Question details
The user is unable to retrieve data from SharePoint using Excel Power Query when the source file names contain special characters.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Connecting to a SharePoint document library folder using Power Query to retrieve and transform file data.
- Observed behavior
- The Power Query SharePoint folder connector fails, producing invalid URL or data format errors when encountering file names with hash (#), percent (%), or dollar sign ($) characters.
Verify that you have edit permissions for the affected files in the SharePoint document library, as you will need to modify the file names to resolve this limitation.
Rename the SharePoint File to Remove Special Characters
Since the Power Query SharePoint folder connector does not support certain special characters, renaming the file is the most direct and effective workaround.
According to the official connector documentation, special characters such as #, %, and $ are not fully supported in Power Query when connecting to SharePoint. The Web.Contents REST API call often fails to process these characters properly, even when URI-encoded.
Open your web browser and navigate to the SharePoint document library where the problematic file is stored.
Right-click the file, select 'Rename', and remove any hash (#), percent (%), or dollar sign ($) characters from the file name.
Return to Microsoft Excel, open your Power Query Editor, and click 'Refresh Preview' to verify that the file is now successfully retrieved.
Submit Feedback via the Excel Feedback Portal
If organizational policies prevent you from renaming files, report the URI-encoding failure to Microsoft's development team.
Try WPS Office for Seamless Spreadsheet Management
If you frequently encounter API limitations or complex connector errors in Microsoft Excel, consider WPS Office. It is a highly compatible, lightweight, and free alternative that simplifies your daily spreadsheet tasks without unnecessary complexities.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
- 2. Open WPS Spreadsheet: Launch WPS Spreadsheet, which offers a familiar interface for all your data analysis needs.
- 3. Work with your data: Easily open your synced SharePoint or local Excel files directly and start working immediately with high compatibility.
Frequently Asked Questions
Which special characters are known to cause errors in the Power Query SharePoint folder connector?
According to Microsoft's documentation, characters such as the hash or number sign (#), percent (%), and dollar sign ($) are generally unsupported and can cause the data retrieval process to fail.
Why does Power Query fail even if the hash character is correctly URI-encoded?
The SharePoint folder connector utilizes the Web.Contents function, which translates a simple GET request into a REST API call. Due to current API limitations, functions like getfilebyserverrelativeurl struggle to parse certain URI-encoded characters, resulting in an invalid URL error.
Are there any automated workarounds to replace hash characters in Power Query?
Unfortunately, the error occurs at the initial connection and retrieval stage before data transformation steps can be applied. Therefore, you cannot use Power Query's 'Replace Values' function to fix the file name; the file must be renamed directly in SharePoint.
