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.
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.
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.
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.
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.
A second interesting thing is that you will be presented with options to connect to either a report or a dataset.
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.
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
After logging in, you will then see a new blank Pivot Table connected directly to the Power BI Service
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.