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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
IgorGershenson
Helper II
Helper II

Unable to use Power Query with OneDrive for Business

I want to link my power BI query to a file on OneDrive. Since others will be using this file as well, I am trying to connect to the file on OneDrive using Web where I enter the URL for my file. I get the URL by going to the file itself and then copying its path. When I do all this, Power BI says access is forbidden. This is odd since I use OneDrive all the time. Please help. 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey,

 

i'm not 100% sure if I understand how you retrieved the path to your Excel file residing in OneDrive. As this is not that obvious as one might thing I will provides some more detailed description and also some screenshots.

 

First login to O365 (either login.microsoftonline.com or office.com). Navigate to OneDrive and open the Excel file, maybe Excel online will open the Excel file. If the file starts your Excel desktop version you can skip the next step otherwise, just read on.

 

Open the xlsx in Excel desktop from within Excel online

image.png

 

After Excel desktop has opened in the Excel file is active, go to the File menu and select Info:

image.png

Now hit the text just right under the file name with the left mouse button:

image.png

Select the command "Copy path to clipboard"

image.png

Open a text editor of your choice and paste the path to the editor and remove the last characters including the quesion mark.

image.png

After you have removed the last characters copy the remaining path, go to your Power BI Desktop, choose "Get Data", choose Web as data source and paste the path

image.png

One last thing, you have to choose "Organizational account" to authenticate:

image.png

 

Hopefully this provides the missing link.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
TomMartens
Super User
Super User

Hey,

 

i'm not 100% sure if I understand how you retrieved the path to your Excel file residing in OneDrive. As this is not that obvious as one might thing I will provides some more detailed description and also some screenshots.

 

First login to O365 (either login.microsoftonline.com or office.com). Navigate to OneDrive and open the Excel file, maybe Excel online will open the Excel file. If the file starts your Excel desktop version you can skip the next step otherwise, just read on.

 

Open the xlsx in Excel desktop from within Excel online

image.png

 

After Excel desktop has opened in the Excel file is active, go to the File menu and select Info:

image.png

Now hit the text just right under the file name with the left mouse button:

image.png

Select the command "Copy path to clipboard"

image.png

Open a text editor of your choice and paste the path to the editor and remove the last characters including the quesion mark.

image.png

After you have removed the last characters copy the remaining path, go to your Power BI Desktop, choose "Get Data", choose Web as data source and paste the path

image.png

One last thing, you have to choose "Organizational account" to authenticate:

image.png

 

Hopefully this provides the missing link.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks again. Power BI now works but I am still having issues with using Web connector in Excel Power Query. I guess I need to do a similar enterprise authentication in Excel but I am not sure how. Looks I am signed into my Microsoft account so not sure why I am getting the error: "access to the resource is forbidden"

 

 

 

 

 

 

Thank you. The only part I was missing was the authentication. Awesome!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.