How can I connect to an excel source (or any source) on onedrive using Power BI Desktop?

[MiguelMartinez]

Trying to keep the connection live when I upload to the Power BI service to turn on autorefresh.

[RobertBruckner · Accepted solution]

Here are the steps for OneDrive for Business / O365 Group file locations to get this working:

1. Make sure to install the latest Power BI Desktop from here: https://powerbi.microsoft.com/en-us/desktop
2. In the Power BI Desktop ribbon select Get Data -> Web Contents -> Specify the <file url>  
• Next to the file in OneDrive click   and select “Details”. This should show you a “Link” field. Copy the contents of the Link field, and remove the “?” and everything after it (the guid). Examples of correct file urls:
• https://microsoft-my.sharepoint.com/personal/robruc_microsoft_com/Documents/Data.xlsx 
• https://microsoft.sharepoint.com/teams/pbianalysis/Shared Documents/Data.csv  
• As an aside, you can actually see the real correct file url at the bottom of the browser window as tooltip when you hover with the mouse over the file name. Not sure why SharePoint Online is making more and more UI changes to obscure the real underlying file url – the urls with access tokens etc. aren’t supported by SharePoint APIs and throw errors when used for programmatic access against SharePoint APIs.

3. For the authentication mode in Power BI Desktop, select Organizational Account and sign-in with your organizational account. 

4. After publishing to PowerBI.com, click on the dataset, and setup a data refresh schedule. Use OAuth2 as authententation mode and sign-in with your organizational account in the browser.

Considering that SharePoint Online / OneDrive increasingly obscure the real file url in the UI, for a future update of Power BI Desktop we will look at adding a new top-level “Get Data” entry specifically for O365 OneDrive / OneDrive for Business, and in addition automatically “fix up” the SharePoint/OneDrive exposed urls by removing the “?” and guid-junk at the end.

[mwmckee]

For Power BI groups, I discovered the URL format is slightly different.  

The following worked for me:

https://mydomain.sharepoint.com/sites/mygroup/Shared Documents/myfolder/myfile.xlsx

I hope this saves someone some time.

[mwmckee]

*** Update:  This is working now.  This was me not responding to the prompt correctly after receiving the error.  What solved it was clicking on "Organizational account" in the grey left-hand side of the dialog; then I was able to establish a connection to the Excel data source.  It took a few attempts at signing in as another user before it worked.

*** For Power BI groups, I discovered the URL format is slightly different.  The following worked for me:

https://mydomain.sharepoint.com/sites/mygroup/Shared Documents/myfolder/myfile.xlsx

Thank you for the clear steps.

But, unfortunately, it's not working for me.

I'm getting the error "Access to the resource is forbidden" despite trying some slight variations of this URL:

https://mydomain-my.sharepoint.com/personal/myuser_name_mydomain/Documents/myfile.xlsx

I get the same forbidden error having chosen each one of the provided options below in response to "Select which level to apply these settings to:"

https://mydomain-my.sharepoint.com/

https://mydomain-my.sharepoint.com/personal

https://mydomain-my.sharepoint.com/personal/user_name_mydomain

In Power BI Desktop, I have signed in with my organizational account, the same one for Office 365 and OneDrive for Business.

I'm running Power BI Desktop 2.33.4337.281 64-bit (March, 2016).

[djnww]

@RobertBruckner, any update on this ?

I know the workaround solution works for most small-medium sized companies, but as a government organisation, it could take up to a year before we are allow for oAuth2 authentication.

Cheers,

Daniel 

[fso]

Thanks @RobertBruckner for the detailed explanation.

I was wondering if this can also work with a Personal OneDrive The URL structure is very differnt and I cannot get the same process to work.

Best,

Ferdinand

[george]

@fso, I also found it difficult to obtain the URL in a Personal OneDrive, but one workaround is to download the file, then go to your browser's downloads page and copy the URL from there.

[Anonymous]

somehow I updated the spreadsheet on OneDrive not reflected in my report in Group Workspace. Creating a new report from the data source shows my update in the spreadsheet. any idea?

[konstantinos]

What do you mean live? You can connect to any source (excel, csv ) from onedrive business if you pass a valid path and authenticate. Then you can schedule aytorefresh. Both free and pro account since is a web source.

Is this what you meant?

https://medium.com/@Konstantinos_Ioannou/onedrive-powerbi-desktop-use-valid-paths-to-import-data-sto...