cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ausphil Regular Visitor
Regular Visitor

Refresh works in PowerBI Desktop but not in Service

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ausphil Regular Visitor
Regular Visitor

Re: Refresh works in PowerBI Desktop but not in Service

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.

7 REPLIES 7
ausphil Regular Visitor
Regular Visitor

Refresh works in PowerBI desktop but not in Service

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

 

Super User
Super User

Re: Refresh works in PowerBI Desktop but not in Service

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?


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

"Proud to be a Datanaut!"
ausphil Regular Visitor
Regular Visitor

Re: Refresh works in PowerBI Desktop but not in Service

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.

 

PowerBI Troubleshoot.PNG

Super User
Super User

Re: Refresh works in PowerBI Desktop but not in Service

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?



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

"Proud to be a Datanaut!"
ausphil Regular Visitor
Regular Visitor

Re: Refresh works in PowerBI Desktop but not in Service

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.

jckingst New Member
New Member

Re: Refresh works in PowerBI Desktop but not in Service

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 Regular Visitor
Regular Visitor

Re: Refresh works in PowerBI Desktop but not in Service

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.