Web.Contents fails with '%' in the filename

[MladenJ]

Hi, 

I hope you can help me to get this issue resolved.

What the goal is: Load the Excel file (.xlsx) from a SharePoint folder. It seems pretty straightforward thing to do.

I am able to do this like in the steps below:

1. Open the file from SharePoint using web browser and open it with desktop app
2. Copy the file link (located under File/Info/Copy path)
3. Go to Excel and use Get Data/From Web (remove the "?web=1" at the end of the URL)

Now, this works for all files, except the files that have '%' character in the filename. 

Why is that?

Basically, this works:

https://company.sharepoint.com/Folder/Folder/Folder/Test file.xlsx 

And this doesn't work:

https://company.sharepoint.com/Folder/Folder/Folder/Test file 10%.xlsx 

The error i keep getting from PQ "(404): Not Found", but the file works perfectly fine.

Can you please guide me on what i should do next?

[MladenJ · Accepted solution]

Hey all, thanks for your comments.

Unfortunately the Web connector is off the table, as there is no workaround for the special characters issue.

@v-kelly-msft - thanks for the article you posted, but it doesn't apply to my case here. Even with the direct link to the file, i'm unable to connect.

Luckily enough, the only workaround i managed to apply is to use the One Drive synced folder and Folder.Files to get to the desired folder. 

I guess that i'll have to make sure everyone syncs their One Drive before the use 🙂 

[v-kelly-msft]

Hi  @MladenJ,

Try to use a try/otherwise statement to handle this issue,see the blog below:

https://blog.crossjoin.co.uk/2016/08/09/handling-404-not-found-errors-with-web-contents-in-power-que...

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

If a filename contains certain special characters, it may lead to authentication errors due to the filename being truncated in the URL. 

As @mahoneypat suggested, try the SharePoint Folder connector instead of Web. 

[mahoneypat]

I would try to use the SharePoint Folder connector instead of Web, and then click on the "Binary" for that file to extract it.

Regards,

Pat

[MladenJ]

Not everyone has SharePoint folder connector unfortunately, so it needs to go through Web.

Only possible solution i could think of is to go through synced One Drive folder, but then again, not everyone has the folder synced.