Refresh works in PowerBI Desktop but not in Service

[ausphil]

Data sources are two excel files on company Sharepoint. Initial dataset setup was done through PowerBI Desktop -> Get Data -> From Web and entered the URL of the files in format: https://COMPANYNAME.com/DEPARTMENT/EXCELFILE.XLSM

When I check Data Source Permissions settings, "Credentials" are set as Windows. When I refresh in PowerBI Desktop everything works fine.

When I use the "Refresh Now" option in PowerBI service, I get the following error:

Hide On demand ‎3‎/‎26‎/‎2018‎ ‎3‎:‎00‎:‎01‎ ‎PM ‎3‎/‎26‎/‎2018‎ ‎3‎:‎00‎:‎02‎ ‎PM Failed There was an error when processing the data in the dataset.
Hide details
Message: 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. Table: ProductionTable.
Cluster URI: WABI-CANADA-CENTRAL-redirect.analysis.windows.net
Activity ID: f04ddb7e-ca9e-42ec-997e-465c273343df
Request ID: 8401d897-a21d-c352-a73f-c730347fb581
Time: 2018-03-26 22:00:02Z

When I click on Edit Credentials, it looks like it is set as anonymous, but when I try to change it to Windows Authentication I get the error:

Failed to update data source credentials: Hide details
Activity ID: f04ddb7e-ca9e-42ec-997e-465c273343df
Request ID: 26006607-63bd-a0b0-07be-c5705259ddb2
Status code: 400
Time: Mon Mar 26 2018 15:21:16 GMT-0700 (Pacific Daylight Time)
Version: 13.0.4759.170
Cluster URI: https://wabi-canada-central-redirect.analysis.windows.net

Any thoughts or suggestions? Thanks!

[ausphil]

Data sources are two excel files on company Sharepoint. Initial dataset setup was done through PowerBI Desktop -> Get Data -> From Web and entered the URL of the files in format: https://COMPANYNAME.com/DEPARTMENT/EXCELFILE.XLSM

When I check Data Source Permissions settings, "Credentials" are set as Windows. When I refresh in PowerBI Desktop everything works fine.

When I use the "Refresh Now" option in PowerBI service, I get the following error:

Power BI

Hide
On demand ‎3‎/‎26‎/‎2018‎ ‎3‎:‎00‎:‎01‎ ‎PM ‎3‎/‎26‎/‎2018‎ ‎3‎:‎00‎:‎02‎ ‎PM Failed There was an error when processing the data in the dataset.
Hide details
Message: 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. Table: ProductionTable.
Cluster URI: WABI-CANADA-CENTRAL-redirect.analysis.windows.net
Activity ID: f04ddb7e-ca9e-42ec-997e-465c273343df
Request ID: 8401d897-a21d-c352-a73f-c730347fb581
Time: 2018-03-26 22:00:02Z

When I click on Edit Credentials, it looks like it is set as anonymous, but when I try to change it to Windows Authentication I get the error:

Power BI

Failed to update data source credentials: Hide details
Activity ID: f04ddb7e-ca9e-42ec-997e-465c273343df
Request ID: 26006607-63bd-a0b0-07be-c5705259ddb2
Status code: 400
Time: Mon Mar 26 2018 15:21:16 GMT-0700 (Pacific Daylight Time)
Version: 13.0.4759.170
Cluster URI: https://wabi-canada-central-redirect.analysis.windows.net

Any thoughts or suggestions? Thanks!

[GilbertQ]

Hi there

I would go into the Power Query Editor, click on your table where you imported it from SharePoint.

I would then click on the Gear Icon next to Source and see what type it has set it to with regards to the data type from the Web. It sounds as if this has changed to HTML, instead of Excel?

[ausphil]

Hey Gilbert, thanks for the reply and suggestion.

Is this what you're talking about? If so, both of them are set as Excel workbook.

[GilbertQ]

I am not sure why it is not working then.

I would suggest ensuring that you possibly re-create the data source in the Manage Gateways in the Power BI Service?

[ausphil · Accepted solution]

Figured out the issue.

Our Sharepoint is on-premises.

Installed On-Premises Gateway, added company sharepoint address as "Web" data source not "Sharepoint".

Works now.

[Anonymous]

Can you be more detailed in how you reached your solution? I too am running into this error and I don't understand how to solve it.

[ausphil]

Hi, is your sharepoint on-premises?

If so you will need a data gateway installed to bridge the gap between on-premises and the cloud.

Read about it here: https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem

I had my IT department create a virtual machine that's always on, and I set up the on-premises data gateway on it.