During the Microsoft Data Insights Summit Keynote, James Phillips and Amir Netz announced that the feature of “Analyze Power BI models in Excel” is coming very soon. That it actually appeared THAT soon was not on my radar (but it’s not surprising – it’s just the Power BI way of work )
Today, the Power BI team announced the March 2016 Update of Power BI Desktop including several new features (just have a look at the https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-march-update-feature-summary/). But even better – Power BI service got updates too .. and those updates include the ability to analyze Power BI models in Excel.
I will guide you through a quick walkthrough about how to use that feature. For this demo, I will use the sample data set (and reports) “Human Resources Samples”.
The starting point for Excel analysis is either the menu of a data set or a report. Within these menus you’ll find the action “Analyze in Excel”.
Installation & Configuration
When you click on one of these actions, Power BI service gives you the hint (or brings to your attention the requirement) of a local Excel update.
Attention: this dialog highlights the setup media for x86 – which I tried first and failed because of my 64bit Excel. Therefore – check your Excel installation type (x86 or 64bit) and install the appropriate software (which is an updated AS OLE DB Provider for SQL Server 2016).
After that, the software requirements should be met and the analysis can start. Depending on your browser the connection file is either opened directly or downloaded to your Downloads folder. In my case (I used Chrome) the connection file is downloaded and just a double-click away
With a double-click, Excel loads and the first thing is a security information dialog asking if you really want to open that kind of file (in days of crypto-viruses a good way – but as I am sure this file is the downloaded one from Power BI I enabled this connection).
Next step is authentication – You’ll need to enter you credentials for Power BI in order to access the data.
If you entered the right credentials, Excel establishes the connection to your Power BI model and shows the well-known PivotChart Fields control. This control is your starting point for your journey to Power BI data in Excel!
Analyze your Data
Just start creating your tables and charts as you do with other data sources.
Et voila – here’s a test report. I tried to re-recreate a part of a report from the sample dataset.
- The first try was an easy one – only one thing retarded me a little bit (Excel 64bit).
- Personally I think this is a great step forward for pushing Power BI and getting more (and additional) ways of analyzes to the masses.
- The performance in my demo looked fine – I will definitely try it with huger datasets. The data set used was no hybrid one – this will be another challenge (just to mention performance and security).
With that, happy testing and analyzing in Excel!
- The documentation of the feature is already available. I was too fast in the initial publish of this blog post. https://powerbi.microsoft.com/en-us/documentation/powerbi-service-analyze-in-excel/