One of the primary benefits of working in a cloud-based environment is the ability to access data with any device and any application at any time. Connecting to data stored in the cloud means that data can always be up to date when you access it, which means as long as there is an internet connection, you will also always be up to date.

In Microsoft Power BI, you can create reports and dashboards that will always contain the latest data by connecting to a file stored on OneDrive. When you or your team update a file on OneDrive, the changes are disseminated to everyone with access to that cloud storage. Changes on OneDrive can also be communicated to apps like Power BI, saving time and increasing overall productivity.

Connecting to data on OneDrive with Power BI is similar to connecting to any other file, but there are some additional procedures to keep in mind.

SEE: Microsoft Power BI: The smart person’s guide


To connect to an Excel file located on OneDrive, follow the steps we showed you in How to create your first Microsoft Power BI dashboard. Only this time, choose a different location. In my example, I can choose from Local Files, OneDrive (Personal), OneDrive for Business, and a SharePoint Team Site (Figure A). For the purposes of keeping the data connected to Power BI up to date, the two OneDrives and the SharePoint location would all work.

Figure A

In this example, we connected to an Excel workbook located on the OneDrive for Business server. Be sure to choose the Import connection.

Using the sample data of a simple report of salespeople and their respective sales of products and geographical areas (Figure B), I created the simple Power BI report shown in Figure C.

Figure B

Figure C

Because the data connection was made to a file on OneDrive, any changes made to that Excel workbook will automatically flow to the Power BI report. For example, let’s say Mark makes a big sale of Widgets in Europe. When that new data is entered into the Excel workbook, we see a change in our report, similar to Figure D.

Figure D

Note: The default refresh rate for Power BI reports is one hour. To see more immediate results, you will likely have to refresh the data manually by clicking the Refresh button on the Ribbon of your report.

SEE: Microsoft wants to bring better data visualization to your enterprise

Dynamic data

Just by connecting to data stored on OneDrive or SharePoint, users can keep their Power BI reports and dashboards continuously updated automatically. This not only saves time, it also eliminates the need to remember to update reports that need to be disseminated to a team, department, or even an entire company. You can’t get much more productive or efficient than that, can you?

The Power BI how-to series

This tutorial is the third in a series of tips and tricks that will help you reap the benefits of Power BI and data visualization. The series also includes these topics:

The entire series is available as a PDF ebook.

Also read…

Your thoughts

Are you one of the lucky few who “get to” prepare and share daily reports? Wouldn’t you like to automate as much of that task as possible? Share your thoughts and opinions with your peers at TechRepublic in the discussion thread below.