DataSource.Error: The downloaded data is HTML, which isn't the expected type.

[JGauthier]

Hi everyone,

I have been using Power Query to import Data from Excel files located on an On-premise SharePoint site for a while.

For an unknown reason, none of my reports are working now. I keep getting the following error when I connect to the source:

DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server

The only instruction I have in the query is the following: 

Excel.Workbook(Web.Contents("https://mysharepointsite/123456/Finance/TestData.xlsx"), null, true)

I've tried to clear permissions, it doesn't work.

I can open the source file directly without any problem so it isn't a permission issue on SharePoint.

I recreated the connection many times, it doesn't work.

I've copied the Excel data file over a SharePoint Online site that I have and and changed my URL to point to it and it works!

I am clueless as to what is going on my on-prem SharePoint. Any idea ?

thank you

[Carreira]

Hello @JGauthier - have you figured out a solution for this? I have same issue, I am geting data from a personal OneDrive excel file to my PBI, it was working perfectly for 7 months, now same error:

DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server.

[v-yingjl]

Hi @JGauthier ,

Due to on-premise SharePoint site, the credentials for the report should be used as Windows credentials, instead of the organizational accouts credentials, you can try to check the credentials.

Refer: SharePoint On Premise Connection to Excel File 

Best Regards,
Community Support Team _ Yingjie Li

[JGauthier]

This is exactly what i am doing. I use Windows Credentials and it doesn't work, whereas for my other test with the source on SharePoint Online, it is with Organizational Account.

Is there a way to see what's that HTML web page message is telling ?

thank you.

[v-yingjl]

Hi @JGauthier ,

Whether you can tried to use Power BI Desktop to connect to the web site to check it has the same issue or not, you can use the Web connector which is built in Power BI Desktop to connect it directly.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JGauthier]

Hi,

I've tried Power Desktop (File/Excel then my SharePoint URL) and it works.

Does that help to figure out why the connector within Excel doesn't work ? That's the one I need.

thanks!

[Vijay_A_Verma]

Try following

Open Power Query - File - Options and Settings - Data source settings - If data source is not visible, select Global permissions - Double click on your source - Delete credentials - Now refresh your query

[JGauthier]

I had already tested that and cleared all permissions in the workbook and the global ones.

Still have the same problem 😞

[Vijay_A_Verma]

Delete already existing connection and refresh your query.

[JGauthier]

doesn't work either. I even created new connections in new files and still the same result.