SharePoint Online Document Library

[v-mifole]

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.

[Strubkj]

This says completed, but how does it work? I can't seem to use the contents of an excel file stored on SharePoint as a connected data source. I can do this in the Power BI online tool, using the get data feature, but not on Power BI Desktop? Can someone walk me through the steps? I am a bit new at this. Thanks!

[GuyJohnson]

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.

[jonas_claes]

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.

[mshifman]

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."

[Fyouri]

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.

[thiago_castro]

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?

[skmckenzie]

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])

[markkravmacrae]

'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!

[marian_rotaru]

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)?

[jesse9]

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.