Direct Connect from Excel to Power BI Service

Level: Beginners

Today Microsoft announced a great new feature that allows you to direct connect FROM Excel TO Power BI and not the other way around.  This simple change really streamlines the integration experience between Excel and the Power BI Service, and makes Power BI even more like you own personal SSAS server.

Power BI direct connect

There are 2 immediate use cases that come to mind

Personal SSAS Server

It is now easier than ever to effectively have your own free SSAS Server.  Just create a free Power BI account, load up your Power BI workbook, and then access your data models directly from within Excel.

Corporate Sharing

If you are using the paid Pro version of Power BI, the ability to use Power BI Service as a “bonus” SSAS server is also now easier than ever.  Just open Excel and connect directly to the data models that have been shared with your from your organisation from the Excel Power BI menu.

Installing and Getting Started

Download the new version of the Excel Dashboard Publisher Addin here

After installing, you will notice a new Menu Bar in Excel.  Click on this menu and then connect to your Power BI Service account.

image

Once you are signed in, you will be presented with a prompt asking how you want to connect.  In my case I was offered just a single workspace as shown below.

image

What is interesting is that I actually have 2 workspaces in my service, but I can only see one of them.   So something seems a bit amiss here, but I am sure that will be sorted out shortly.

image

A second interesting thing is that you will be presented with options to connect to either a report or a dataset.

image

In my testing, the results were the same.  Maybe something new is coming in the future here!

And yet a third interesting thing is that I was then asked to Authenticate again.  It would be nice if these Azure Authentication dialog boxes were not modal and supported Lastpass.  This would make the login process so much easier.  Even better – Excel could pass the stored credentials to the service so I don’t have to authenticate twice.

image

The good news is this is a one off additional login process, so the next time you connect from Excel you won’t have to log in again.  The exception is if you have multiple Power BI accounts (eg if you are a consultant like me with many clients with different domains) – in that case you will have to continuously log in and out, and not be able to benefit from Lastpass Sad smile

After logging in, you will then see a new blank Pivot Table connected directly to the Power BI Service

image

A Final Word

The original Pin to Power BI Dashboard/Pin Manager is still there (see 1 below), giving you great flexibility in sharing ranges in your workbooks in the Power BI Service.

And last but not least, look how POWERful my Excel Ribbon has become (2 below) – I now have 5 POWERful Addins.  Sweeeet.

image

Share?

Comments

  1. Great post Matt, I personally think as you also wrote above that Power BI will become the SSAS Tabular PaaS. It is getting a lot of the On-Premise features and I think it is almost there.

    In my mind this will soon be a valid option in order to leverage SSAS Tabular from the cloud. Without having to actually install or configure the SSAS Tabular server. But be able to leverage all the benefits!

  2. Just tried to ‘Connect to Data’ and got the following message: ‘You don’t have any data in Power BI’ – I definitely do, lol so undeterred and based on my experience with AIE funny messages I tried again…..Bingo!
    No ODC files or any of that with AIE, seamless connection by pivot to my Power BI dataset

  3. Dear Matt,

    what version of Excel (of Office) are you using for all these great add-ins?

    thanks a lot,
    Alessandro

  4. Been working with this the last few hours on 2013 and getting a lot of Excel crashes
    Don’t know whether the experience will be different on 2016
    Regardless, i’m all over this and can live with crashes in the short term (just need to crank up my incremental save routine again)
    Seeing the data refresh in your workbook from your Power BI hosted source for the first time puts the kind of expression on your face that the caveman had when he was introduced to the wheel

    • I agree. I think one reason Power Pivot was (is?) a slow burn to success is that MS did not turn on the Power Pivot tab by default in the ribbon in Excel 2013. I hope that it will add this new Power BI tab to the ribbon by default in future updates, as this will drive adoption in my view.

  5. The experience is indeed significantly better in 2016 so will use this platform going forward

  6. Matt, Thank you for all these great posts! I just tried it out in Excel 2010 and it worked perfectly for me. Even better I can use cube formulas to combine multiple data models into one report in Excel!

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x