Power Query unable to load xlsx files from sharepoint folders when filename oder folder contains #

[Anonymous]

Hello Community,

I wanted to pull data from Excel sheets from a specific Sharepoint folder. Trying to do this I discovered the following behaviour: If a path- or filename in Sharepoint includes a # sign at any position, Power Query will return the following error message to me:

DataFormat.Error: The input URL is invalid. Please provide a URL to the file path on SharePoint up to the file name only (with no query or fragment part).

To double check my assumption I loaded all files from our Sharepoint site, filtered for Extension ".xlsx" and a value in Content that evaluates to an error. For all of these Elements the formula

Text.Contains([Name], "#") or Text.Contains([Folder Path], "#")

did evaluate as TRUE.

This does not happen if the file/folder does not include a #. Since the folder in question cannot easily be renamed I'd like to know if anyone knows a workaround for this behaviour without renaming folders.

[timg · Accepted solution]

Hi Felix,

Unfortunately it looks like this is a limitation: https://learn.microsoft.com/en-us/power-query/connectors/sharepointfolder#filename-special-character... 

Best regards,

Tim