Unable to use Power Query with OneDrive for Business

[IgorGershenson]

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. 

[TomMartens · Accepted solution]

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

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

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

Select the command "Copy path to clipboard"

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

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

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

Hopefully this provides the missing link.

Regards,

Tom

[IgorGershenson]

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"

[IgorGershenson]

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