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.
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.
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.
Hi,
I have a local dataset in excel and a local powerBI file, both are personal files.
I don’t want to use my work email address and don’t understand why I need to connect to a microsoft PowerBI account just to be able to connect two local files !?
Is there a way to connect to powerBI from Excel ?
Thanks
Gav
Well the reason you need to do it this way is this is the way it is designed. Microsoft has spent billions of dollars building this software and it only asks you to pay once you use sharing or the integration with Excel. Putting that aside, you can look at my blog article here https://exceleratorbi.com.au/excel-workbook-connection-local-power-bi-desktop/
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!
Wow, I hadn’t thought of connecting to multiple models. Great idea
The experience is indeed significantly better in 2016 so will use this platform going forward
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.
Great Post! Question: I have the PowerPivot tab, but how do you get the PowerBI tab?
The Power BI tab comes from the download Addin I linked early in the article.
I wonder if this add-in works for Office 2016/365 versions that are not hard-wired for Power Pivot, like Office 365 Home.
Good question. Does anyone have O365 Home Edition and can test?
Damn you are fast Matt. Excellent post.
Dear Matt,
what version of Excel (of Office) are you using for all these great add-ins?
thanks a lot,
Alessandro
I am using Excel 2013. I don’t know if it works with excel 2010
LastPass definitely needed!!
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
Wow, this is even better. Installing today!
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!