How to Use and Refresh Power BI Using OneDrive Data Sources
Using an Excel file in OneDrive for Business allows multiple users to update data, connect to a data source and schedule an automated refresh without having a lot of infrastructure. One of the challenges for users with Power BI is being able to use a data source that you can update and flow those changes to your Power BI dashboards and reports. The following walkthrough will show you how to leverage this feature for your solution.
Security on the File.
OneDrive for Business is set up as a SharePoint document library. You can see this in the links and security setup. The link to the file we are using provides an example;
A note for later, you will need to remove the “?web=1” when we use the link.
In your document management routines, you should not create special security for each file in a directory. This will lead to confusion as you try to maintain good security practices. You should, at a minimum, use the folder to maintain security, and you can allow all files within the folder to have the same security. If you need different security on a file, create a new folder.
For Power BI, you do not need to give the users of your dashboards access to the folder but an account that you will use to allow Power BI Refresh Service to access the folder. If you want a group of people to update the Excel worksheet, then you maintain security at the folder level or share the file just as any other file in OneDrive or a SharePoint library.
Get the URL to the File
First, one of the most common mistakes in this process is how you get a link to share the file. Once you have the Excel file in a OneDrive folder, navigate to the file. If you click on More Detail, three horizontal dots, you will have various options, as shown below. This may seem logical, especially if you use SharePoint, but this is a sharing link you provide someone in an email and will not work as a data source for Power BI . This will work to a point but will fail when you schedule a refresh.
The correct way of getting the file link is straightforward, however, you must get it from the opened file. From the OneDrive web page go to the File menu and click on the three dots. The following menu is displayed, select Open in Excel. This will load the file on your local machine.
Once the file is open in Excel, click on File -> Info and right click on the path listed just above the Protect Workbook dropdown. This will bring up a popup dialogue. Select the Copy Path to Clipboard item.
We can now use this link in Power BI to connect to this workbook as a data source.
Add the connection to Power BI Desktop file.
Now that we have the URL path to the file, we can use this to add the data source to a Power BI Desktop file. From the Power BI desktop menu, select the Get Data down arrow. Even though this is an Excel file, it is exposed as a web connection because of the URL. Select the Web source.
Paste the link into the From Web dialog and remove the “?web=1” string at the end of the link; otherwise, the updates will fail. If Power BI Desktop prompts you for credentials, choose either Windows (for on-premises SharePoint sites) or Organizational Account (for Office 365 or OneDrive for Business sites). Note: For on-premises SharePoint sites, you will need a Gateway.
As shown below, you will have characters after the <<filename>> .xlsx, remove those and click OK.
You will (may) get a message that “Access to the resource is forbidden” and a request for credentials. Select the Organizational Account.
Select your organizational account and the file or folder in the drop-down box to which you want the access credentials to apply. This is normally the folder if you are keeping with recommended security practices. Try and avoid having many files in a directory, each with different security credentials.
Select the Sign–In Button. As shown below, you may get a few options if you have used several accounts to access SharePoint or OneDrive sites. You will be asked to verify your credentials if you have two-factor authentication activated.
The first box will ask you what credentials you need to enter. In our example, we are using a OneDrive for Business file as a data source in the Power BI service. When enabling Service Refresh for that file, select OAuth2 as the authentication method when configuring your refresh settings. Anything else may cause an error, such as “failed to update data source credentials”, in an attempt to connect or refresh. Selecting OAuth2 as the authentication method will allow you to avoid that credentials error.
Once you have entered your credentials you will be brought back to the Connect dialogue.
You may get a prompt saying that the Session cannot connect, and the following popup is presented. You want to select what type of object you are trying to connect to, which is an excel file. Select OK to continue.
You now have the Navigator dialogue where you select the worksheets and ranges to import, as pictured below.
Extra Tip: Ensure you have set up the Excel data as ranges, and do not import the entire worksheet. This could bring in garbage data you might have in your worksheet. An earlier blog post, 5 Steps to Improve Your Excel Data Visualization Productivity, has more detail on using Excel as a data source in Power BI.
Publish & Schedule Updates
When ready to publish your reports, you will select Publish on the main Home Toolbar in Power BI Desktop. This will publish your solution to the Power BI subscription you are currently logged into.
When you publish your reports to Power BI, in order to schedule the data refresh, you must sign in again on PowerBI.com to give permissions to the refresh process. When you publish workbooks to Power BI the credentials are not transported for security reasons. Go to your Datasets settings for the Data Source, select the Schedule Refresh and sign in there.
As you can see below, the Edit Credentials option is highlighted under the notice that the credentials are invalid or missing in this case. When setting a refresh schedule, Power BI will connect directly to the data source using connection information and credentials in the dataset to query for updated data, then load the updated data into the dataset.
Select Edit Credentials; this brings up the same authentication conversation as above. Select OAuth2 and enter your credentials. These will be saved and allow you to schedule the refresh.
Once you have entered and logged through, select the Scheduled Refresh option, as highlighted below. There is an option for once a week or hourly each day. This will allow you to schedule eight updates a day as a maximum.
Select the Time Zone, and the Time, and Apply. You can also add other times if you wish, up to a maximum of 8 updates a day. Don’t schedule updates that conflict with the file.
I usually create an Audit Workbook with a simple table with global settings I want to use in a solution. For this example, I set up the schedule, changed the global settings in the Excel worksheet from “Five” to Six”, waited for the scheduled update, then validated that the change occurred. The sample is illustrated below.
Before:
Change the data & save:
After the scheduled refresh:
As the screen capture below shows, navigating to the data set and selecting the three dots, “…”, the last refresh is displayed at the top. You can also go to Schedule Refresh and see the updates’ history (2).
As the capture below shows, selecting the Refresh History will bring up the most recent refreshes and their completed or failed status.
Conclusion
Using OneDrive in selected solutions is a low-footprint way to share data that does not include sharing connections to on-premises data. Other options, such as the Data Gateway, are provided in the references below if you need to store your worksheet on-premises.
SharePoint document libraries can work the same way, as can OneDrive Personal, which are referenced below.
Reference:
Use OneDrive for Business links in Power BI Desktop
Refresh a dataset created from an Excel workbook on OneDrive, or SharePoint Online
