Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I get an error when loading the file into PowerBI. I've tried multiple times and consistently get this error:
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
Will data imported from "Get Data -> Web" refresh correctly? It seems to be working but I haven't been able to test refresh yet.
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?
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
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?
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
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?
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
Yes, I couldn't find any other way to generate sharepoint credentials.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |