400 Error with SharePoint Data Source due to Apostrophe in Folder Name in SharePoint

[muen-chen]

This looks like a bug with escaping apostrophes (single quotes) in string handling with the IDataReader interface when Power BI is talking with SharePoint. Removing the apostrophe in the folder name and re-running worked. Please refer to Power BI's error message below.

An error occurred while processing the data in the dataset.
Refresh failed:

xxxxxx has failed to refresh.
Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.
SharePoint: Request failed: The remote server returned an error: (400) Bad Request. (The expression "Web/GetFolderByServerRelativePath(decodedurl='/sites/xxxxx/xxx O'Connell xxxxx')/ListItemAllFields" is not valid.). The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: xxxxx.
Next refresh for xxxxxx is scheduled for August 26, 2021 5:30 UTC.

Technical Information

Activity Id:
830df42b-0a24-48fb-85d7-491525d27abc

Request Id:
8d650435-83e8-43ec-9e68-18bb77bd0080

Cluster Name:
WABI-AUSTRALIA-EAST-A-PRIMARY-redirect.analysis.windows.net

Refresh Start Time:
August 26, 2021 1:30 UTC

Refresh End Time:
August 26, 2021 1:40 UTC

[v-chuncz-msft]

@muen-chen 

A similar issue has been reported internally. 

IcM 162413559

You will need to submit an assistance request to the SharePoint Online team for them to further investigate why does SharePoint reply to our call with an HTTP 400, only when the char ' is present. With any other special chars, like "#","%","&" it's working well.

For the ' char, this can be easily reproduced outside of PBI desktop by simulating the same call on the browser window.