Error loading Excel file from Sharepoint

[jamieo]

I get an error when loading the file into PowerBI.  I've tried multiple times and consistently get this error:

Something went wrong.

Failed to configure selected file
Please try again later or contact support. If you contact support, please provide these details.

Activity ID198f55e3-c7e4-4564-8a0d-dec856a7a742

Request ID00a889db-595c-5b71-4ab2-9369f95d082a

Correlation IDeea9878b-8266-bf1f-cbf9-2eef81213669

Status code400

TimeWed Aug 17 2016 10:21:11 GMT-0500 (Central Daylight Time)

Version13.0.1605.323

Cluster URIhttps://df-msit-scus.analysis.windows.net

[v-yuezhe-msft]

Hi @jamieo,

How do you connect to the Excel file in Power BI? Please make sure that you go to “Get Data>Files>SharePoint -Team Sites” and enter the URL of SharePoint Site, then choose the target Excel to connect to.

In addition, you can use Power BI Desktop to connect to Excel stored in SharePoint and check if it is successful. In Power BI Desktop, use “Get Data>Web” option and enter URL of Excel file.


Thanks,
Lydia Zhang

[jamieo]

Will data imported from "Get Data -> Web" refresh correctly?  It seems to be working but I haven't been able to test refresh yet.

[jamieo]

I did as you say to connect to the document and was able to see and select the desired Excel file, just not load it.

If I do as you suggest, would I still be able to refresh the dataset when published to PowerBI.com?

[v-yuezhe-msft]

Hi @jamieo @toulou,

When you connect to Excel file from Sharepoint Online in Power BI Desktop, create PBIX file and publish it to Power BI Service, you are able to schedule refresh for the dataset.

Moreover, OAuth2 is the right protocol for SharePoint Online Excel, there is no other option to connect to the web URL besides those options mentioned by @toulou.

Thanks,
Lydia Zhang

[toulou]

yes, oAuth2 works. Although I had to open and close the browser a few times because after a few files it wouldn't work anymore. Thank you for your help.

[jamieo]

Lydia, even going this route, the automatic refresh fails.  I can manually refresh through the "Schedule Refresh" interface by manually changing to Oauth and providing credentials, but these credentials are gone the next time I login and any scheduled refresh fails.

Is there any way to get scheduled refresh without using a PowerBI Gateway?

[v-yuezhe-msft]

Hi @jamieo,

No gateway is required in your scenario. Power BI Service directly connect to the Excel file stored in SharePoint Online. What is the exact error message do you get when you schedule refresh?

Thanks,
Lydia Zhang

[jamieo]

Every time I login the credentials appear to revert to "Anonymous" and the error I get during the refresh is:

```
Hide details
Data source type:

Processing error:
The key didn't match any rows in the table.
Cluster URI:
DF-MSIT-SCUS-redirect.analysis.windows.net
Activity ID:
7c0c7083-e50f-4e5b-a0fd-4f06b029db04
Request ID:
ed5aa13c-4c61-4fe4-8a33-8db11624f317
Time:
2016-08-22 05:21:05Z

```

p.s. Is there any way to get markdown to work in these forums?

[v-yuezhe-msft]

Hi @jamieo,

When entering the account and credential to configure schedule refresh in Power BI Service, do you use the same organization account that is used to connect to SharePoint Online in Power BI Desktop?


Thanks,
Lydia Zhang

[jamieo]

Yes, I couldn't find any other way to generate sharepoint credentials.

[toulou]

I am having the same issue. I tried to connect to an Excel file hosted on a SharePoint Site. I selected Get Data-->File-->Excel. The File selection window opens then. 

In the path I put: https://XXX.sharepoint.com/sites/XXX/Shared%20%Documents/XXX/data/prod/

When I click enter I do get the following error message in Power BI Desktop: Windows can't find 

https://XXX.sharepoint.com/sites/XXX/Shared%20%Documents/XXX/data/prod/. Check the spelling and try again. The path is correct. I tried the same path in Excel 2016 Power BI and it works seemless. 

I then connected to the file using Get Data-->Web and put in the full SharePoint Path. Power Bi Desktop was able to connect the file after provding Organizational Credentials for the Connection. That worked so far.

Now I published the Power BI Desktop Report to Power BI Online.

The problem now is that Power BI App cannot connect to the data files (and hence not schedule refreshes). I do get the same error message like jamieo.

Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again.

Activity ID:	0430926a-98ae-4fb1-9797-aa0da2fa0b27
Request ID:	eb024ef8-941a-5643-9aa8-1136d7b5933c
Status code:	200
Time:	Thu Aug 18 2016 11:29:44 GMT-0400 (Eastern Daylight Time)
Version:	13.0.1605.328

I have the option to enter credentials for the web URL. However, the problem is that only Anonymous, Windows, Basic and oAuth2 are available but not Organizational Account. 

How can I solve this issue either changing the initial connection method (I don't have the option Get Data-->Files-->SharePoint-Team Sites by the way in Power BI Desktop) or the authentication in the Power BI App?