Power BI–Export Data (To Excel)

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 Smile)

image

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.

Walkthrough Demo

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”.

image

image

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.

image

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).

image

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 Smile 

image

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).

image

Next step is authentication – You’ll need to enter you credentials for Power BI in order to access the data.

image

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.

image

Et voila – here’s a test report. I tried to re-recreate a part of a report from the sample dataset.

image

image

image

Conclusion

  • 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!

Wolfgang

 

Update:

image

Advertisements
This entry was posted in Azure, Business Intelligence, Cloud, PowerBI, SQL Server 2016 and tagged . Bookmark the permalink.

6 Responses to Power BI–Export Data (To Excel)

  1. Hi!

    Do you know if there’s a way to put this data source on powerpivot?

    The feature is great, bu i want to use some cube formulas to get more from this.

    by the way, gret site man!

  2. Pingback: Power BI–Export Data (to Excel)–Where to find the Excel updates!? | workingondata

  3. Pingback: Power BI–Export Data (To Excel)–Drillthrough is here! | workingondata

  4. Pingback: Power BI–Export Data (To Excel)–Available to Free AND Pro Users & Enhancements for multiple user accounts | workingondata

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s