Power BI and Excel
Use Excel and Power BI together and create powerful, connected reports and visuals.
You will learn how to...
Introduction to using Excel data in Power BI
Welcome to the Power BI and Excel section of the Guided Learning course for Power BI.
Chances are good that you've used Excel before. Maybe you used Excel to create or view reports, or to build pie charts or other visuals. Or maybe you used Power Pivot or Power Query to build solutions that were a bit more technical. If so, you'll be right at home in Power BI, and so will your workbooks.
This section shows you just how easy it is to bring Excel workbooks into Power BI, and demonstrates that Power BI and Excel make a great partnership.
Introduction to using Excel data in Power BI
Watch an introduction to the ways Excel and the Power BI service work together
With Power BI, getting your Excel data into Power BI is easy.
The following topics guide you through uploading an Excel workbook with a simple table into Power BI. Then you see how to upload workbooks created with Excel's more advanced BI data modeling and reporting features, such as Power Pivot and Power View.
You'll also learn some benefits of saving your Excel workbook files to OneDrive for Business. When you connect to Excel files on OneDrive from Power BI, your reports and dashboards in Power BI are updated and refreshed automatically when you make changes and save your work.
Upload Excel data to Power BI
In this topic, we'll first take a look at how you can import an Excel workbook file containing a simple table from a local drive into Power BI. You'll then learn how you can begin exploring that table's data in Power BI by creating a report.
Make sure your data is formatted as a table
In order for Power BI to import data from your workbook, that data needs to be formatted as a table. It's easy. In Excel, you can highlight a range of cells, then on the Insert tab of the Excel ribbon, click Table.
You'll want to make sure each column has a good name. It will make it easier to find the data you want when creating your reports in Power BI.
Import from a local drive
Wherever you keep your files, Power BI makes it easy to import them. In Power BI, you can use Get Data > Files > Local File, to find and select the Excel file we want.
Once imported into Power BI, you can begin creating reports.
Your files don't have to be on a local drive, of course. If you save your files on OneDrive or SharePoint Team Site, that's even better. We'll go into more details about that in a later topic.
Start creating reports
Once your workbook's data has been imported, a dataset is created in Power BI. It appears under Datasets. Now you can begin exploring your data by creating reports and dashboards. Just click on the Open menu icon next to the dataset and then click Explore. A new blank report canvas appears. Over on the right, under Fields, you'll see your tables and columns. Just select the fields you want to create a new visualization on the canvas.
You can change the type of visualization and apply filters and other properties under Visualizations.
Import Power View and Power Pivot to Power BI
If you use any of Excel's advanced BI features like Power Query (called Get & Transform in Excel 2016), to query and load data, Power Pivot to create powerful data models, and Power View to create dynamic reports, you can import those into Power BI, too.
If you use Power Pivot to create advanced data models, like those with multiple related tables, measures, calculated columns, and hierarchies, Power BI will import all of that as well.
If your workbook has Power View sheets, no problem. Power BI will re-create them as new Reports in Power BI. You can start pinning visualizations to dashboards right away.
And here's one of the great features of Power BI: If you use Power Query or Power Pivot to connect to, query, and load data from an external data source, once you've imported your workbook into Power BI, you can setup scheduled refresh. By using scheduled refresh, Power BI will use the connection information from your workbook to connect directly to the datasource and query and load any data that has changed. Any visualizations in reports will automatically be updated, too.
Connect OneDrive for Business to Power BI
Get seamless integration between Power BI and Excel when you save your Excel workbooks to OneDrive.
Because OneDrive is in the cloud, just like Power BI, a live connection is made between Power BI and OneDrive. If you make changes to your workbook on OneDrive, those changes are automatically synchronized with Power BI. Your visualizations in reports and dashboards are kept up-to-date. If your workbook connects to external data sources like a database or an OData feed, you can use Power BI's Schedule refresh features to check for updates. Need to ask questions about data in your workbook? No problem. You can use Power BI's Q & A features to do just that.
There are two ways to connect to your Excel files on OneDrive for Business:
- Import Excel data into Power BI
- Connect, manage, and view Excel in Power BI
Import Excel data into Power BI
When you choose to import Excel data into Power BI, table data from your workbook is loaded into a new dataset in Power BI. If you have any Power View sheets in your workbook, those are imported and new reports are automatically created in Power BI, too.
Power BI will maintain the connection between it and the workbook file on your OneDrive for Business. If you make any changes to your workbook, when you save, those changes will be automatically synchronized* with Power BI, usually within an hour. If your workbook connects to external data sources, you can setup scheduled refresh so the dataset in Power BI is kept up-to-date. Because visualizations in reports and dashboard in Power BI will use the data from the dataset, as you explore, your queries are lightning fast.
Connect, manage and view Excel in Power BI
When you choose to connect to the Excel workbook, you'll get a seamless experience of working with your workbook in Excel and Power BI. When you connect this way, the workbook's report has a small Excel icon next to it.
In the report, you see your Excel workbook in Power BI just as you would in Excel Online. You can explore and edit your worksheets in Excel Online by selecting Edit from the ellipses menu. When you make changes, any visualizations you've pinned to dashboards are updated automatically.
No dataset is created in Power BI. All of the data remains in the workbook on OneDrive. One of the many advantages to this approach is that you can setup scheduled refresh if your workbook connects to external data sources. You can select elements such as PivotTables and charts and pin them right to dashboards in Power BI. If you make any changes, they're automatically reflected in Power BI. And, you can use Power BI's awesome Q & A features to ask questions about the data in your workbook.
Excel in Power BI - summary
In this collection of topics, you learned how easy it is to leverage your existing Excel workbook files by uploading and exploring them in Power BI. You can upload Excel workbooks with simple tables or workbooks created with Excel's more advanced BI features like Power Pivot and Power View.
You also learned how to connect to Excel files that reside on OneDrive for Business, and use Power BI's automatic refresh features to keep your reports and dashboards up-to-date as you make changes in your workbook.
Congratulations! You've completed the Power BI and Excel section of the Guided Learning course for Power BI. That was easy, wasn't it? Power BI and Excel make a great team, and makes it easy for you to use them together.
With all the knowledge you've gained, and your ability to create compelling visuals in reports and dashboards, you're probably ready to share your masterpieces. The next section, Publishing and Sharing, shows you how to do just that.
See you in the next section!
You've completed the Power BI and Excel section of Microsoft Power BI Guided learning.
You learned how to...
- Introduction to using Excel data in Power BI
- Upload Excel data to Power BI
- Import Power View and Power Pivot to Power BI
- Connect OneDrive for Business to Power BI
- Excel in Power BI - summary