OneDrive & PowerBI Desktop— Use valid paths to import data stored in OneDrive for Business

How to import data in PowerBI Desktop stored in OneDrive Business, upload in #PowerBI service and schedule refresh ( valid as of end of August 2015 )

PowerBI service & OneDrive for Business are really working great together and as long as we have an O365 subscription we can use it to store all excel and PBI desktop data models so they can refresh and reflect data model / report changes every hour.

But since we store all data model files we can use it to store also data files that we use as data sources instead of local copies or sync OneDrive folders.

But connecting to OneDrive needs passing the right path to PBI desktop or powerquery in order to connect to data files and this can be tricky. We will clear this up.

I will use PBI desktop but same stuff apply in excel powerquery too.

Open PBI Desktop and choose Get Data -> Web

We need to copy a valid path that point to our excel/csv/text data file in OneDrive

We can try copy the path from browser in

  1. Document view

2. File shared link

3. or even the path of the file when we open it

All the above will return and errors as query editor cannot recognize them as valid paths.

OneDrive for Business path in browser usually looks like this (below ) .We only need to keep the bold text ( replace it with your domain&user)

https://companydomain-my.sharepoint.com/personal/username_companydomain_com/_layouts/15/start.aspx#/Documents/Forms/All.aspx

Then we add the file name and extention (i.e excel.xlsx ) and if needed before the folder that the file is stored. The valid path will be like

https://companydomain-my.sharepoint.com/personal/username_companydomain_com/Documents/FolderName/excel.xlsx

Now that we entered a valid path we have access to the file contents.

Expand the column ‘Data’, do any desired transformations and then load the data as any other data source.

The same path link construction is also apply when we need to access files from Sharepoint Online team sites..

Follow the above instructions and your sharepoint online team site valid path will look like this

https://companydomain.sharepoint.com/sites/teamsitename/Shared Documents/FolderName/excelxlsx

The coolest thing is that now you can upload the PBI desktop file in #PowerBI service and schedule refresh using your O365 credentials.

Select ‘Get Data’ and then choose ‘Files’

Navigate to the PowerBI desktop file..Either option is good enough but since we use OneDrive for Business is better to store also the pbix file there and connect to PowerBI.

After few moments the dataset, reports & will be imported to service so go to ‘Datasets’ and click the ellipsis right the file name. Then select ‘Schedule Refresh’

Select Edit source credentials — under the ‘Data Source Credentials’ , in the window opens select oAuth2 Authentication Method and finally enter your organisational account credentials.

Select the refresh interval you need ( usually after the data source file in OneDrive will be updated )

And all set..Now we have everything stored in OneDrive for Business and the PowerBI refresh configured..

Note: No need for Personal DMG since the source is online and supported by PowerBI for scheduling data refresh.