Setup PowerBI to refresh from OneDrive

The objective is to save Excel files to a OneDrive folder, then use them as a data source when developing a report in PowerBI Desktop.

When the report is published to PowerBI.com, a refresh schedule can be setup to reach into OneDrive and retrieve the data from the files.

Tip: Make sure that the folders and file names do not contain spaces. This avoids the spaces being replaced by’%20%, which reduces readability.

Create the folders in OneDrive

Create a folder structure in OneDrive.

\Report\
\Report\Data\
\Report\Data\Data-Values\
\Report\Data\Data-Static\

Prepare the data in Excel

Create the data files in Excel and save them into the folders.

Get the web folder path

Get the web folder path from OneDrive.

Open OneDrive in a web browser and navigate to the folder containing the file.

Click on the three dots to the right of the file name, Choose Open and Open in App.

Choose Excel if prompted.

The file will be opened in Desktop Excel.

Click on the File menu.

In the Info section, under the file name a path will be displayed.

Left click on the path and select Copy path to Clipboard.

Paste the link into a text editor like Notepad.

https://cloud-my.sharepoint.com/personal/rob/Documents/Hyp%20view/Max/Data/Static/Static%20Data%20-%20Max.xlsx?web=1

Remove all characters after the file extension, <File name>.xlsx

https://cloud-my.sharepoint.com/personal/rob/Documents/Hyp%2view/Max/Data/Static/Static%20Data%20-%20Max.xlsx

Create the report in PowerBI

On the Home menu select Get Data - Web

Set the Radio button to Basic.

Paste in the URL from the previous step.

Click on the OK button.

Select the tables that you want to include and click the Load Data button.

Create your report.

Save it to your base folder.

Publish the report

On the Home tab click the Publish button.

Click on a Workspace and then click on Select button.

Add login Credentials

Open PowerBI.com

On the left menu, expand Workspaces and select the workspace that you published the data to.

Still using the left menu scroll down to Datasets.

Select the Data set and click on the three dots.

Select Schedule refresh.

Then in the centre, select Datasets and then click on your dataset.

Expand the Data source credentials

Click on Edit credentials

Set the Authentication method to OAuth2.

Set the Privacy level to Organizational.

Click on the Sign in button.

Sign in with your office account.

Set-up a Scheduled refresh

In the same place that you navigated to in the previous section (Datasets - Your Dataset), select Scheduled refresh.

Set the Keep your data up to date switch to On.

Set the Refresh frequency to Daily.

Set the Time zone.

Add one or more times.

It can be refreshed on the hour or half hour.

Tick Send refresh failure notices to the dataset owner

Add an e-mail group address to Email these users when the refresh fails.

Click the Apply button.

Tip: Don’t schedule refreshes for the same file at the same time.

Refreshing

The data will automatically be pulled from OneDrive based on the schedule that you created.

To manually refresh the data, select the workspace using the left menu.

In the centre section select Datasets and then click on the Refresh now icon.

The last and next refresh date time will be displayed.

Once the dataset has been refreshed, click on Reports and open the report.

At the top of the report click on the three dots and select refresh.

Warning: If the data source file is open on your desktop, the refresh will not work.