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:

• Get Data From OneDrive for Business (this post)
• Creating Power BI Visuals
• Creating Power BI Card Visuals
• Sharing Power BI Reports (for External Users)
• Sharing Power BI Dashboards

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 Post

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