Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
MiguelMartinez
Microsoft Employee
Microsoft Employee

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

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

Data for Everyone! We mean it
1 ACCEPTED SOLUTION
RobertBruckner
Microsoft Employee
Microsoft Employee

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:
    • 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.

 

View solution in original post

8 REPLIES 8
RobertBruckner
Microsoft Employee
Microsoft Employee

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:
    • 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.

 

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.

*** 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).

@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 

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
Regular Visitor

@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
Not applicable

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
Memorable Member
Memorable Member

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

Konstantinos Ioannou

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.