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.
Example:
- 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.
Conclusion
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.
Hi,
when you have two data sources one from Database and from Ondrive how do you configure in the Gateway. Also when connecting the One drive file in power bi desktop if you use Organizational as the authentication option. And in Gateway you dont have such option. How to do it.
You can configure the Gateway for each of the data sources. You must ensure for the database connection that the ServerName is exactly the same name as the Data Source Name for the Gateway to work.
Depending on how you set up your data source in the Gateway it can possibly have the Privacy settings. Just as a side note if you have different privacy settings for different sources they will not allow you to refresh the data due to a conflict. I typically set the Privacy settings to Ignore to avoid this happening.
Hi Gilbert, I just found this post on your blog and read it very carefully. I’ve been struggling for day with the same scenario you describe in this post, but with the addition that in my Power BI Desktop dataset, besides the Excel file in OneDrive for Business, I have another data source which is an Azure SQL Database. In other words, I have a very similar scenario as Arun’s above, but my two data sources are in the cloud so I am not using a personal or enterprise gateway. In Power BI Desktop, I’ve configured access to the OneDrive for Business spreadsheet with my organization account and a privacy level of “Organizational” (because Power BI Desktop does not let me choose “None”) , I’ve configured access to the Azure SQL Database using “Basic” (user name and password for the SQL Database) and privacy level of “Organizational” following your advise above. Whenever I refresh the information in Power BI Desktop, everything works perfectly. Then I published to the Power BI Service and I can see that I have only one data source listed (which I assume encompassed both of my data sources ¿?). I have gone to the data source configuration and under the Credential of Data Origin I can see update options to update credentials for both, the SQL Database and a “Web” data source. I’ve updated both credentials as Basic and “Organizational” privacy for the SQL database and as oauth and “Organizational” privacy for the Web data source so the match with what I did in Power BI Desktop. The problem is that the SQL database seems to be refreshing well, both manually and under a schedule every one hour, but the Web data source doesn’t. I can’t make it refresh manually and it doesn’t refresh under a schedule with by the way I can’t see any schedule options for it under Schedule Cache section in the Data Sources configuration section in Power BI Service. Sorry for the long message, I’ve tried to be as clear as I can in the hope that you could advice about what could possibly be wring with what I’ve done. Any help will be much appreciated. Thanks in advance!
Hi there
Thanks for the message and it would appear that by connecting it would appear that when connecting to a file in OneDrive there is no opportunity to be able to set the schedule. That is what I have seen when looking to potentially be able to have files automatically uploaded to OneDrive and then try and schedule the refresh.
Does the above make sense?
Hi Gilbert, thanks for your prompt response. I was able to make it work in this way: In Power BI Desktop I connect to the Azure SQL Database not with the Direct Connection option (I was previously using this option) selected but with the Import option selected. For the Excel worksheet in OneDrive for Business, I connect to it entering the URL just as you describe above. Then I I publish to Power BI Service, it seems that it recognizes that the Azure SQL Database is good for Direct Connection and somehow Power BI Service connects to Azure in this way because everything is working perfectly now and I do not have a gateway installed. I didn’t have to configure my credentials for neither the Azure connection not the OneDrive for Business connection as Power BI Service marked them as OK in the Data Source config section. Power BI Service is giving me the option of scheduling refreshes by entering the time for each refresh in a section called Schedule Refresh and when a refresh time is due, both the Azure DB and the Excel spreadsheet refresh. I can also trigger manual refreshes and the work the same. Yes, I think the schedule refresh options refer to the Azure SQL Database and not to the OneDrive spreadsheet as you say, but somehow both data sources get refreshed. At the end of the day, things is working for me now, but on the other hand, honestly I really don’t fully understand how the whole thing works ; ) Thanks again and have a great one!
Hi there
Thanks for the comment and if I had to try and put it into one sentence it would be because both sources live in the Microsoft/Azure cloud there is no need for a gateway.
I hope that makes sense?