Power BI – Connecting to OneDrive for Business from Power BI Desktop & Refresh from Power BI Service without the need for the On-Premise Gateway
In this blog post I am going to show how to connect to a file on OneDrive for Business in Power BI Desktop, then upload to the Power BI Service and configure it to refresh from within the Power BI Service. And as in the title you will NOT need the On-Premise Gateway to enable your data to be refreshed.
- I have a file that I have already uploaded to my OneDrive for Business called indicator_hours per week.xlsx
- And this is the file that I am going to use in this example.
Getting the URL for your file stored in OneDrive for Business
In the steps below I am going to show how to get the URL from OneDrive for Business.
- As with my example I logged into OneDrive for Business and went to my file.
I then selected the file, when you do this in the ribbon at the top it will change to say Open, click on Open and then select Open in Excel
This then opened on my local version of Excel
- NOTE: I got a warning prompt. I clicked Ok.
Due to my version of Excel being 2010 I was prompted to log into Office 365
- NOTE: If you are using a newer version of Excel and you have already connected your Office 365 account this step might be skipped.
- It then opened in Excel
Then what I did was I clicked on File and then Info and at the top you can see it has got the direct link to the file.
NOTE: If you are using a newer version of Excel and have your Office 365 account connected it should look like the following below.
- Now I copied the URL and pasted it into Notepad so that I could use it in the steps below.
- One thing I did check was to make sure that the URL ended with “xlsx” if there is anything after the “xlsx” remove it as it is not needed when connecting with Power BI Desktop.
Connecting to the OneDrive for Business file in Power BI Desktop
In the steps below I will show you how I connected to the OneDrive for Business file in Power BI Desktop.
- I opened Power BI Desktop and then selected Get Data.
From the list I selected Web
- Then I clicked on Connect.
Next it is going to ask for the URL, now I copied the URL that from my Notepad that I had saved earlier.
- I then clicked OK.
Now when the Navigator window opened up, it looks like the all too familiar Excel Navigator.
- I then selected my Data table and clicked Edit, because I wanted to edit my data and make some changes before loading it into the Power BI Desktop Data model.
Once I had brought in my data and shaped it this is the report that I created
I then uploaded this into an App Workspace I created called Public Shares
Configuring the OneDrive for Business Data refresh in the Power BI Service
The final step in the process is to now configure the data refreshing from within the Power BI Service.
- I logged into the Power BI Service and then went to App called Public Shares.
I then clicked on Settings and then Settings
Then once in the settings I then clicked on Datasets and I could see my “One Drive Connected File” dataset
When I clicked under Gateway connect I saw that it is already setup with “Connect directly”
Then next I needed to configure the Data source credentials to connect to OneDrive for Business
- So I clicked on Edit Credentials
I then selected OAuth2
- And then clicked on Sign in
- I then got prompted to log into Office 365 and put in my email address and password.
Once successfully logged in I got a notification that it was successful
Now I was ready to actually setup a Scheduled refresh by going to “Scheduled refresh”
- I then enabled the “Keep your data up to date”
And then I also added a few times that I wanted my data to be updated as shown below.
- I then clicked Apply.
I could then wait for it to schedule above, but if I wanted to test it now I could go down to the datasets on the right hand side, click on the ellipses (breadcrumbs) and click Refresh Now
And once done I could then see that it was refreshed successfully.
You can see how I connected to a file in OneDrive for Business as well as enabling it to be refreshed. This can enable a user in the organization to be able to access and refresh data easily leveraging OneDrive for Business.