Skip main navigation

£199.99 £139.99 for one year of Unlimited learning. Offer ends on 28 February 2023 at 23:59 (UTC). T&Cs apply

Find out more

Connecting to Excel Workbook on OneDrive for Business

WILL: One of the other things that we can do with Excel is use OneDrive for Business as a place to store our Excel content. And manage it using things like OneDrive's check-in, check-out, and version control capabilities. That's really useful, particularly if you want to make changes to that Excel workbook over time. And the great thing is we can then connect to that workbook, connect into Power BI, and retain that connection so that any changes to the workbook get automatically picked up by Power BI as well. I'm going to start with an Excel workbook that I've already uploaded to OneDrive for Business. So I've got an Excel file already uploaded to my OneDrive for Business here. Just a simple sales report.
And when I open it in OneDrive for Business, you can see I've got a pivot table on one sheet and I've got another sheet here which has got some Power View content in it. So this is actually a workbook that had Power Pivot in the data model set up and Power View sheets as well.
Now if I go over to Power BI, I can go and connect to that workbook. All I need to do to get data, I'm going to bring in a file. And again, this time I'm going to choose the OneDrive for Business option. So as I said, connecting to OneDrive for Business like this means that you have a live connection to that file. So either, so whenever anything happens like if you go and add more rows to Excel tables or if you go and update the Power View reports or the data model, those changes will get synchronized to Power BI as well. So I'm going to select the file and hit connect. And I get an option here.
I can either import that data into Power BI. And what that's going to do is connect the two together and actually go and create Power BI reports and a power BI data model within Power BI. The data get stay, the data stays in OneDrive and it's just being refreshed on a schedule, as you would do for any other data refresh. The other option on the right hand side is to leave all of that stuff in Excel and just view it. The charts, pivot tables, worksheets, everything that's in that file through Power BI. So let's start with the import option.
This will stop processing the file. And as I said, that's going to split up into a data model and into Power BI reports. It's going to be putting out those Power View sheets into reports So you can see here's the dataset and the report is just being loaded. So I've got the same charts and graphs from that Power View sheet copied over here. Now it's worth noticing that if I make any changes to the Excel file, changes to the Power View sheets there, they are override changes in this Power Bi report.
I'm going to delete those and go through the process again to talk about the other option. So again, I go to get data, files, OneDrive for Business.
And this time let's just do the connection option. So I'm just leaving that content in OneDrive for Business and just surfacing it, just viewing it through Power BI. So now we'll see over on the left hand side, rather than the dataset and report being created, I just see the report. And you can see on the left hand side there's a little Excel icon to show you that this is, something is being managed through Excel. If I choose edit, it'll open in Excel services in edit mode and I can do things like scheduling refresh for this as well.
When I hit that I get through to the same schedule refresh screen as I would do for any other dataset within Power BI. But you can see on this workbooks tab, this workbooks tab gives me all of the Excel sheets that are being managed through Power Bi, and the credentials and gateway status et cetera for all of those. I have to do the same thing as I would have done with other sources in terms of telling Power BI what credentials to use.
And I'd set up the schedule for the refresh in just the same way.
And here, I got the option to say, go and keep my data up to date and then specify the frequency with which it will go and refresh. And what that's actually doing is going over to OneDrive and saying, "Okay, OneDrive, we need you to go and refresh this Excel workbook. We need you to go and do the refresh as if it was just going through OneDrive." But it's Power BI that's managing it. Power BI that's managing the schedule, and Power BI that's storing the credentials and things that the refresh happens with. And again, you'll need the personal gateway set up if you're going to go and connect back to any on-premises sources through that Excel workbook. So that's it.
It's as simple as uploading up to OneDrive for Business, uploading your Excel workbooks to OneDrive for Business and then connecting it through Power BI and setting up the date refresh from there.

When you connect an Excel workbook stored in OneDrive for Business, Power BI will retain the connection so that any changes to the workbook get automatically picked up by Power BI as well. Power BI will have a live connection to the file. Here’s how you can set that up.

This article is from the free online

Microsoft Power BI: Advanced Data Analysis and Visualisation

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education