Get Data From OneDrive for Business

2016-09-07
Ken Puls
Categories: Excel, Power BI, Power Query

It’s been a few weeks since I’ve posted a technical blog post, and I need to get back to it, so here goes. This is going to be a five part post on how to Get Data From OneDrive for Business, (using Power Query,) which will allow us to create a dashboard that reads from an Excel file hosted in the cloud. By the time you get to the end, you'll see the full chain of how you can get from Data to Dashboards, as well as schedule them to just keep updating.

In addition, as a bit of a departure for me, I’m going to demo this using Power BI Desktop, although connecting to the data this way will work with Excel as well (creating the visuals would of course be different.)

Series Table of Contents

As mentioned above, this is the first in series of blog posts, which build on each other to create an overall solution from Data to a Power BI dashboard. You’ll want to follow along in order, so here’s the table of contents for the series:

In this installment I’ll show how to get data from OneDrive for Business. Over the next four blog posts, I’ll show how I created some visuals, published it to a public facing web page, and how I set up the scheduled refresh to keep it up to date.

Architecture Background

Before we get into how to connect to the data, let’s just talk about where the data lives and why I decided to architect the solution using the route I did.

Folder Setup

Some time in the past, I went and connected to my OneDrive for Business folder, synching it to my laptop. This allows me to create Excel files locally to store my data, but have them available in the cloud as well, and even access and update them via Excel on my iPhone.

Obviously I don’t have a ton of folders in my OneDrive for Business folder, but the key one I’m after is a file called Hotel Stays.xlsx which resides in the Hotel Stays folder. This is the file that I use to track the number of nights I’m not sleeping in my own bed. As I travel a lot now, I figured that this might be interesting to track.

Excel File Setup

The structure of the Excel file is fairly simple. It has a single table in it (called “Stays”), and I update it with a record for each day that I’m away from home:

I think this is pretty straight forward, the only nuances here are:

Every hotel night is logged as a new line
There are independent columns for the street address, city, province and country

So the cool thing here is that I can log updates from my PC or via my phone to keep this up to date. As I don’t capture a ton of info it’s actually pretty quick to do.

Other Architecture Decisions

Now I could have just connected to the Excel file locally, but I really want to host this as a report and schedule refreshes in the Power BI service. To do that, I can’t really read from the local copy of the file, as that would require me to configure a personal data gateway and also to ensure that my PC is always on. As I travel with my laptop I can’t guarantee that so – to allow scheduled refresh without errors – the data needs to read from the cloud hosted version.

So basically, what I want to build is an architecture that looks like this:

Get Data From OneDrive for Business

So let’s get started on this, and see where it takes us...

What Connection Do We Use?

As it turns out, in order to connect to an Excel file that is stored in a OneDrive for Business folder, we actually need to connect to a SharePoint folder and drill into the Excel file we want. It’s kind of like using the classic From File –> From Folder method, only targeted at a web hosted link.

The only problem is… what address do you throw in there if you’re working from your OneDrive for Business folder?

I’ll admit that this wasn’t the most intuitive to figure out. To work it out, here’s what I did:

I logged in to my Office365 control panel at http://login.microsoftonline.com
I clicked the big grid icon in the top left, and then the OneDrive icon

This helpfully takes me to a new page that shows all my files in the web browser. As it happens, the URL for this page also contains the information I need in order to be able to connect to the SharePoint folder using Power Query technology.

The URL we need is to be constructed as follows:

https://TenantName-my.sharepoint.com/personal/UserEmailAddress/

So, as you can see, my Tenant Name is xlguru and the email address has both the @ and . replaced with underscores. This is kind of an important thing to realize here… the Tenant Name is not the same as your domain name. This gets chosen when the Office 365 account is created, and you can register several domains against it. (I have 4 domains associated with my xlguru tenant.) So if you aren’t the admin, the steps above may be the easiest way to work out what it is.

Connecting to a SharePoint Folder

Now that we know the URL we need, it’s time to connect to the folder. I got started by creating a new Power BI Desktop project.

In the past we didn’t have a way through the user interface to provide the necessary connection, so we had to write the M code manually. Fortunately we’ve now got a connector to do this for us, it’s the Get Data –> More… –>File –> SharePoint Folder command (in Excel it’s New Query –> File –> SharePoint Folder).

NOTE: If you don’t have this icon in your list, it means that you are on an older version of Power BI Desktop/Power Query in Excel. If you’ve updated to the latest version and you still don’t have it, it’s a bug that will be fixed. You can still do it, you’ll just need to create a blank query and paste in the M code I’ll show you in a bit.

I then pasted the URL to my OneDrive for Business into the URL.

Of course, you’ll then be prompted to sign in. To do this you’ll want to sign in under Organizational Account and provide your Office 365 credentials.

Successful completion will take you to the preview window where you can just click “Edit”:

Quick side question here… does anyone find this dialog is actually useful? I can’t think of a single time that I haven’t clicked Edit to do more work with it, so it’s basically just a pretty way to slow me down.

If You Don’t Have the SharePoint Folder Option

If you don’t have the SharePoint folder connector for some reason, you can get to the same place by doing the following:

Create a new Blank query
Go to the Advanced Editor
Replace the Source line with:

Source = SharePoint.Files("https://TenantName-my.sharepoint.com/personal/UserEmailAddress/")

This will get you a full list of all the files in your OneDrive for Business folder.

Accessing The Data

Now that we’re connected to the OneDrive for Business folder, we can get to the data we want. In my case, I want to dig into the Hotel Stays file and retrieve the data from the Stays table. To do that I now just treat it like any Excel file:

Locate the correct file
Click the “Binary” of the file to expand it

Drill into the Stays table

End result is that I’ve managed to land some nice data that I can use for my visuals:

There is a small manipulation I do want to make to my data before I call this done though. I’ll need a “Full Address” field in order to plot things on a map visual. To get that I:

Selected the Address, City, Province & Country fields
Went to Add Column –> Merge Columns
I chose to use a 2 character separator of “, “ (comma + space)
I called the column “Full Address”

And finally I named the table “Stays” and clicked Close & Apply.

Checking Our Progress

Okay, so looking back on what has been accomplished so far, I’ve managed to:

Create an Excel file with my data
Save the file in a local folder that syncs into my OneDrive for Business folder
Create a Power Query that reads from the web hosted (not the local copy) of that file

This is the groundwork I’ll need in order to create a solution where I can enable auto refresh of the dashboard and publish it publicly. Until then, hopefully this has been helpful to understand how to connect to a OneDrive for Business hosted file.

Next up we're going to look at creating Power BI Visuals.

24 thoughts on “Get Data From OneDrive for Business”

sam on September 10, 2016 at 7:52 am said:

What about connecting to just One Drive / Sky Drive
Ken Puls on September 12, 2016 at 9:21 am said:

Hey Sam,

I'll post on that once I can come up with a reliable way that I can assure is reproducible. I have a workbook that connects to OneDrive which works, but when I tried the exact same steps on another file, it didn't. 🙁
Bjoern Sjut on September 19, 2016 at 1:23 pm said:

thanks for the write-up!

By the way, is there an approach in which I could just drop new csv reports into a onedrive for business folder and use the folder logic to automatically append all files to each other? The usecase I'm seeing here would be some marketing reporting API that gives e.g. daily reports. I could autodownload those and put them on a onedrive for business folder through the onedrive API, have it automatically picked up by PBI.
Ken Puls on September 19, 2016 at 1:26 pm said:

Hi Bjoern,

Absolutely you can do that. I'd upload 2-3 first, then when you connect to the site:
-Filter to just that folder
-Filter to only text files (you may need to set up a custom filter to begin with, as you'll only have text files there currently - this is a future proofing step in case someone accidentally drops an Excel file in there.)
-Click the double down arrow at the top of the content column

At that point, every time you add a new file to your OneDrive for Business folder, it will upload. And when you refresh (or set up scheduled refresh) it will pull them in.
Bjoern Sjut on September 20, 2016 at 12:11 pm said:

Thanks, Ken. I will try that out.
Zachary Richardson on October 7, 2016 at 8:20 am said:

If I update that excel file, how fast does Power BI refresh the data? Is it automatic? Do I need the pro version?
Ken Puls on October 7, 2016 at 8:40 am said:

OneDrive will update the file as soon as your sync client does it. For most purposes that is immediate but if you have a ton of files syncing then it may take some time.

Power BI however, works on a scheduled refresh. So it depends how frequently you set that schedule. (Most I can seem to get is about 8 times per day.)
Ivan Perez on December 7, 2016 at 8:19 am said:

Hi Ken, thank you for you information.

I have the next url https://xxxxxxxx-my.sharepoint.com/personal/iperez_xxxx_com/ but when I try to connect to this url using Sharepoint folder in Power Bi Desktop, I get an error message: Web.Contents failed to get contents from 'https://xxxxxx-my.sharepoint.com/personal/iperez_xxxx_com/_api/web/lists/getbyid('d60d931e-2102-4774-b98e-4a3db94979a2')/RenderListDataAsStream' (500): Internal Server Error

It is my Onedrive for business account but I'm not able to get the files 🙁

Could you help me please?

Regards
Ken Puls on December 7, 2016 at 8:28 am said:

Ah, you don't want Web.Contents, you want one of the following:
-SharePoint.Files()
-SharePoint.Content()

Hope this helps!
Ivan Perez on December 7, 2016 at 10:27 am said:

Thank you Ken for your quick answer

In fact I am using Sharepoint Folder option, and in rhe advance editor section into query i have: Source = SharePoint.Files("https://xxxxxx-my.sharepoint.com/personal/iperez_xxxxx_com/", [ApiVersion = 15])

Several months ago, aprox 3, it works ok, I have not use the report until now, but I have the problem, now I'm not able to get the connection 🙁

Details:
DataSourceKind=SharePoint
DataSourcePath=https://onesofttek-my.sharepoint.com/personal/iperez_softtek_com
Url=https://onesofttek-my.sharepoint.com/personal/iperez_softtek_com/_api/web/lists/getbyid('d60d931e-2102-4774-b98e-4a3db94979a2')/RenderListDataAsStream
Ken Puls on December 7, 2016 at 11:06 am said:

Ivan, I'm not quite sure what to tell you here. The link looks right to me. I was able to come close to reproducing your original error by forgetting to include the https:// but then I get an error that is a "(405): Member Not Allowed" error instead of the Internal Server Error you get.

To me it sounds like there is something wrong on your Office 365... if you have IT, you may want to reach out to them. If you don't, then you may want to log in to Office 365 and try to log a ticket with Microsoft..
Ivan Perez on December 7, 2016 at 2:27 pm said:

Thank again Ken, I will try to review the issue with my 365 admininstrator.

Best regards
Ivan Perez on December 9, 2016 at 8:14 am said:

Hello

The problem has been solver, thank you for your help Ken, I just delete the code ", [ApiVersion = 15]" from the source path.

Regards
EdH on March 1, 2017 at 2:15 pm said:

Ivan, I just had the same problem, and removing the [ApiVersion=15] also fixed it for me. What is odd is some sites it works fine, and others, it doesn't. {shrug}
Ken Puls on March 1, 2017 at 5:35 pm said:

That is so strange... makes you wonder if some are on more recent builds and some are not? At any rate, it's good to know. Thanks for posting!
Pingback: PowerPoint, Auditing, OneDrive and more... - Roundup #59 | Guy in a Cube
Dan Bettes on March 27, 2017 at 10:44 am said:

Hey Ken! Thanks for this -- it worked beautifully on my machine. I'm wondering now if it will work across my team so others on my team can refresh the data at their leisure (I am leaving my organization soon, so they can't depend on my machine or account initiating data source refreshes).

Using the method in this article, the data source for my query is "https://signaldigitalinc-my.sharepoint.com/personal/dbettes_signaldigitalinc_onmicrosoft_com/" The "dbettes" bit refers to my name. When someone else on my team (who I've shared this OneDrive folder with) needs to update this query, their URL would be different in that it'd have their name in the URL, correct? Would this preclude them from initiating a refresh on the data source? If so, can you recommend what steps I take for a "team" solution?
Ken Puls on March 27, 2017 at 11:34 am said:

Correct, Dan. The issue is that OneDrive for Business is a personal folder on the business site, not a team site.

What you want to do is move the solution to the Office 365 Sharepoint team site. You can then connect to the url via https://.sharepoint.com/sites/. So for you it would probably be something like https://signaldigitalinc.sharepoint.com/sites/

Hope that helps!
Dan Bettes on March 28, 2017 at 10:21 am said:

Ken,

Thanks for the quick response! I've set up my Sharepoint Team Site. I assume I bring in the data to Power Query or Power BI in the same way as this article?
Ken Puls on March 28, 2017 at 10:39 am said:

Yep, (I notice the post has eaten part of the URL though...) Just connect to:

https://{your-tenant}.sharepoint.com/sites/{groupname}

So something like

https://signaldigitalinc.sharepoint.com/sites/bireports or whatever you called the group.
Michael Colbert on April 4, 2018 at 4:18 am said:

Ken, I know this is an older post but I have everything working except for the part of the report refreshing each morning. I get a CSV each night from an external party, drop it to OneDrive via Flow and then want to refresh the report to consume yesterday's data. However, when I try to schedule the refresh, the service gives me this message in the dataset settings.

"You can't schedule refresh for this dataset because one or more sources currently don't support refresh."

I've used organizational privacy level on the sharepoint url.
Ken Puls on April 4, 2018 at 10:38 am said:

Hi Michael,

That's odd. Are you sure that there isn't another data source in your file that could be causing the issue?
Matt on September 3, 2019 at 10:44 pm said:

Hi Ken,
I would like to link data from shared folder (Onedrive for Business) from other organizations, which I can access from chrome. I can access the my data from my OneDrive for business, but the shared folder is not sucessful. Can you help to take a look the following error message? Thanks

DataSource.Error: SharePoint: Request failed: The remote server returned an error: (400) Bad Request. (The URI 'https://elevateglobal-my.sharepoint.com/personal/zhibiao_chen_lfvate_com/_vti_bin/ListData.svc?cid=8549329d-fd0b-4711-84dd-51da61ad14c3/' is not valid since it is not based on 'https://elevateglobal-my.sharepoint.com/personal/zhibiao_chen_lfvate_com/_vti_bin/ListData.svc/'.)
Details:
DataSourceKind=SharePoint
DataSourcePath=https://elevateglobal-my.sharepoint.com/personal/zhibiao_chen_lfvate_com
SPRequestGuid=e2d2009f-b0e3-9000-7e0f-6bc2f49b007b
Url=https://elevateglobal-my.sharepoint.com/personal/zhibiao_chen_lfvate_com/_vti_bin/ListData.svc?cid=8549329d-fd0b-4711-84dd-51da61ad14c3
Ken Puls on September 4, 2019 at 10:14 am said:

Hi Matt,

From other organizations? Unfortunately you only get to provide one set of login credentials. So I think you'd need to clear them and re-authenticate in order to get it to connect. 🙁