Power BI - Analyze in Excel

Analyze in Excel is an interesting feature of Power BI (Power BI Service, to be specific) that lets users analyze and explore Power BI data models using Excel. In other words, the datasets that we publish to Power BI Service can be analyzed using Excel (requires Excel 2010 SP1 and later). To get started with this feature, click on the ellipsis next to your dataset, and click on the option ‘Analyze in Excel’ as shown in the screen shot below.

Power BI will download a connection to this dataset in the form of an office data connection (odc) file. 

Click open to open up your dataset in an Excel PivotTable, which exposes all the data points in your data model. You can slice and dice anything by pretty much any field you have in your data model. As you are analyzing the data, the Excel PivotTable will be pulling data from the data model hosted in the cloud (Power BI Service). Please note that the PivotTable in this case won’t let you define aggregation on numeric fields other than what you've already defined in the Power BI dataset. For example, if there is a numeric field in the dataset, you won’t be able to place it under "VALUES" section of the PivotTable to aggregate it if you haven’t defined the aggregation on that field in the Power BI dataset.

Once you have your report ready, you can save it anywhere you like or share it with other users. You can also publish the Excel report back to Power BI Service, however, it will be uploaded as a static Excel file without any interactivity or ability to refresh data, etc. Below is a  screenshot of a sample report created using Analyze in Excel.

I hope this is helpful, please leave a comment if you have a question.