There are many ways in which Power BI and Excel work great together. Today, we will highlight 4 examples of how these two programs complement each other.
Analyze in Excel
The Analyze in Excel feature is useful for those who wish to utilize the Power BI data model and share it with others using other software tools. This feature also applies to using the Power BI data model to create visualizations in Excel via Pivot tables or charts allowing users that are more comfortable in the Excel environment to have the flexibility to access to the power behind Power BI in Excel.
No additional software or downloads are necessary for this interaction, only Excel and a PowerBI.com account.
How to use this feature:
Once you have loaded your data model to Power BI, users can consume it using any tool of their choice with Analyze in Excel. In the datasets or reports section of PowerBI.com, select the ellipsis beside the name of the dataset or report, then select analyze in Excel. A .ODC file will be downloaded to your computer and once this file is opened in Excel, you can immediately start creating Pivot tables, visualizations, and slicers using the Power BI data model that is hosted in Excel.
Watch the demonstrative video below to learn how this feature works.
Power BI Publisher for Excel
This feature is useful for those who have information in Pivot tables or other Excel visualizations and would like to see this information on the dashboard. This feature also eliminates the reconstruction of Excel visualizations in Power BI by directly pinning Excel information to PowerBI.com.
No additional software or downloads are necessary for this interaction, only Excel and a PowerBI.com account.
How to use this feature:
To obtain Power BI publisher for Excel, simply download the Publisher add-in for Excel and enable the COM add-in. Select the Power BI tab that will appear in the ribbon and utilize Publisher. Initial use will require PowerBI.com sign in by clicking the Profile icon. Once you establish the connection between your PowerBI.com account and Excel, you can pin any Excel range (columns, rows, Pivot tables, or visualizations) to a Power BI dashboard.
In fact, you can push your entire Excel workbook to PowerBI.com by going to file > publish > publish to Power BI. This is useful for eliminating the tedious steps that it takes to get your data into Power BI.com like, signing into the service, browsing for the workbook you wish to upload, and this feature makes download time much faster.
Watch the demonstrative video below to learn how this feature works.
Upload Power View reports to Power BI
No additional software or downloads are necessary for this interaction, only Excel and a PowerBI.com account.
This feature is useful for those who have information in Power View sheets and would like to see this information on the dashboard. When logged into PowerBI.com, you can upload your Excel workbook that contains Power Views by selecting the Get Data button at the bottom left corner of the screen. Once you navigate to the file and upload, Power BI automatically recreates any Power View sheet that is imported and is instantaneously useable in Power BI for dashboard purposes or transformations to unique Power BI visuals.
Watch the demonstrative video below to learn how this feature works.
Scheduled refresh in Power BI with Excel reports
Automatic refreshes (with absolutely no human involvement) require a OneDrive for Business account. Otherwise, no additional software or downloads are necessary for this interaction, only Excel and a PowerBI.com account.
This feature is useful for ensuring that information stays current and up to date in your dashboards and reports on PowerBI.com. Once you load Excel data into PowerBI.com, the name of the dataset will be displayed in the datasets section. If your Excel workbook is stored on OneDrive for Business, Power BI will automatically update the workbook every hour without setting any type of scheduled refresh. Setting up a scheduled refresh is simple: under any dataset, select the ellipsis beside the dataset name, then select schedule refresh. From there, you will be taken to the data management setting screen where the names of all your PowerBI.com datasets will be displayed. Choose the dataset you wish you refresh and select Keep Your Data Up To Date. At this point, you can choose the refresh frequency of your datasets and live worry free with always up to date information in Power BI.
Watch the demonstrative video below to learn how this feature works.