cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
IgorGershenson Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Super User
Super User

Re: Unable to use Power Query with OneDrive for Business

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

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Unable to use Power Query with OneDrive for Business

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

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

IgorGershenson Frequent Visitor
Frequent Visitor

Re: Unable to use Power Query with OneDrive for Business

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

IgorGershenson Frequent Visitor
Frequent Visitor

Re: Unable to use Power Query with OneDrive for Business

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"

 

 

 

 

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 369 members 3,902 guests
Please welcome our newest community members: