Michael Foley on 18 Dec 2014 00:22:52
We have many Excel files on SharePoint Online Document Libraries - well structured and maintained and refreshed. It would be great to be able to point to those directly. The OneDrive for Business is similar, but it would mean we have to duplicate the files from our existing SPO team site. So it would be great to access Excel files located on SPO team site document libraries.
- Comments (41)
RE: SharePoint Online Document Library
From Power BI Desktop you can get access to data files on a sharepoint store via query which works fine, but then PBI Desktop can't save or open the PBIX files to the SP document store directly??? So half of the product can access SP...
RE: SharePoint Online Document Library
Naturally, after I follow Mark Macrae's instructions it tells me the user is not authorized. bear in mind I am the global Office 365 admin for the company, and I created the file. Sigh.
RE: SharePoint Online Document Library
I tried to sync the SharePoint Online library to a local folder and then load all the excel files from local folder. The only issue that I found was that the local folder is on a Windows Server and the OneDrive sync client (groove.exe) is working only when I am logged with Remote Desktop on that server. Is there a way to sync the SPO library all the time (even if I am not logged on the remote session)?
RE: SharePoint Online Document Library
'I have found if you choose:
- Web
- Provide the full path to your Excel file on SharePoint
- Select authentication as Organisation and enter your O365 user id/password if needed
- You should then see a dialog box where u pick the tables you want to pull in
- And voila!
RE: SharePoint Online Document Library
The only real viable option which works at the moment is to use Excel. The Data Query allows from SharePoint Folder. Once you have built the Query you want, save the Workbook. Open Power BI Desktop and import Excel File. This will then create the Query for you connected to SharePoint Document Library. You can then work from there. The Query appears to be = SharePoint.Files("https://Tenant/sites/", [ApiVersion = 15])
RE: SharePoint Online Document Library
When I try to access SharePoint from Power BI desktop and follow:
Get data
SharePoint Folder
The following error is displayed:
Details: "Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)"
If I try to go through web I get the following error:
Details: "The input URL is invalid. Please provide a URL to the file path on SharePoint up to the file name only (with no query or fragment part)."
There's not a single way for me to connect neither PowerBI Desktop nor PowerBI.com to my SharePoint instance.
I do have access to the folder and anything in SharePoint as I'm one of the Global Admins...
What should be quite easy is getting more and more difficult.
Any step by step to a 5 year old child in how to do it?
RE: SharePoint Online Document Library
this is possible when you pick the connection option "web" and point to your Excel document. Pick Organisation account as login option.
when you publish to PowerBi configure the refresh interval. unlike the auto (1h) sync with onedrive for business, PowerBi will only be able to sync each day or week. If you have Powerbi pro you can select 8 sync intervals each day.
RE: SharePoint Online Document Library
Tried Tony Dunn's method, but when trying to access web content, I get an error:
"The service returned a state value that doesn't match the value that was sent. This might indicate cross-site request forgery."
RE: SharePoint Online Document Library
I was struggling with this as well. It works, but only if you do it in the web interface. Couldn't find a way to this in the Power BI Studio. Would be really handy if it would be possible in the 2 environments.
RE: SharePoint Online Document Library
This still doesn't work, but is marked completed. Tony Dunn's explanation is confusing at best. I'm new to Power Bi but really need to access the Excel file buried in our Team Site Libraries on SharePoint online.