Problem
Using Power BI web service, i.e., https://app.powerbi.com, you can easily connect to the OneDrive folder and an Excel file in it. Because there is an easy option provided to do that. But what if you want to build your report on your Power BI Desktop application, and you want to connect to an Excel file in OneDrive? Hmm... you won’t find any source or connector available for OneDrive in there. Let’s see how we can do that and what error we get while doing that.
Solution
Let’s jump into the actions directly. There are two main steps to do this - we will copy the Excel file URL, and we will use this URL in the Power BI Desktop application to connect to the web source.
Step 1
So first, let’s copy the URL of the Excel file from OneDrive. Login to OneDrive >> Navigate to your folder >> Select your Excel file.
From the Open menu, click on "Open in app".
Note
Use IE or IE Edge browser so that this will open your Excel file in the local Excel client application. For other browsers like Chrome, it might not work.
Once opened, click on Info tab >> Below the name of the Excel file, you will find a link “OneDrive - …” click on that link as shown in the below screen. Then, click on "Copy path to clipboard". This will copy the OneDrive path of the Excel file. Keep it handy.
Step 2
Now, let’s move on to the desktop application.
Open your Power BI Desktop application. We will be using Web data source option; so, from the "Get Data" menu, select Web option.
It will open a window to enter the web URL. Enter the copied path of the Excel file as shown below in the URL window. Remove the last part of URL, i.e., remove ?web=1>> Then, click OK.
Next screen is authentication window >> if you keep trying windows, basic or anonymous, you will get an error. So, do not try any of these methods.
If you use any of those methods - You might get error “Access to the resource is forbidden” as shown below,
So, to get rid of this error >> you need to use the Organizational account method >> Select that option and click on Sign in button.
It might automatically log in with your Office 365 account or may ask for your credentials. Once done, you should see that you are signed in.
Now click on Connect to proceed further >> You should see tables and sheets from your excel file on the left side panel and once you choose one of the entities you should see your data. Go ahead and click on Load if you don’t want to make any changes in the data, or if you want to do data transformation then click on Edit button. Good luck.
Summary
As there is no direct connector available we had to Use Web data source to connect to OneDrive excel file form PowerBI Desktop application. And use an Organizational account access method to authenticate. Good luck. Hope this helps.