UPDATE 3/29: Recorded a brief demo video of this feature, embedded below. Analyze in Excel is reported to be working for some free accounts but not all (does not work for me). Does work for Paid/Pro accounts. If you use multiple accounts you may need this workaround. Read Aviv Ezrachi’s comment.
Let me start with a proclamation of victory. “Analyze in Excel” is the best news that I heard at the MS Data Summit. Why is it such a big deal? Let me come to that, after I wrap up my proclamation.
It was July 22, 2015 when I submitted the idea for Excel to be able to connect to Power BI data sets (data models). Many of you rallied around that, voting for the idea, keeping it in the top 10 for long periods. Power BI team listened, and James Phillips delivered the announcement in his keynote that this functionality, “Analyze in Excel” is coming to Power BI within the next 30 days.
It is a victory for the community, but it is also a victory for the Power BI team. It is a victory, that we have a product team within Microsoft which really listens to customers. It is a victory that they have fostered a community which works alongside the Microsoft team to improve the product and is fueling the spread and adoption of Power BI.
Why is “Analyze in Excel” for Power BI a big deal?
Stay with me for this one.
Excel => Power BI => ?
As a Modern Excel enthusiast (Modern Excel = Excel + Power Pivot + Power Query = Magic!), I found myself hesitant in embracing the new world of Power BI. Many of those inhibitions have shed away as Microsoft has continued to innovate and deliver an outstanding experience with Power BI. But I could not get over the feeling that going from Excel to Power BI felt like a one-way street.
Excel to Power BI felt like a one way street
You could upload/import an Excel Power Pivot model into Power BI desktop or onto PowerBI.com. But then you could not get it back. You could not get it back in Excel. In the first blog post on the site (yes the very first) Rob called us gridheads, and that we are.
We are Excel gridheads, and no matter how fancy a visualization suite of tools you deliver to us, we often cannot think unless we see data laid out in an Excel grid.
Excel users like using Excel, and now Power BI allows you to continue to do that.
Excel => Power BI => Excel
If you author a data model in Excel, you could publish it to PowerBI.com. But now (ok, soon) you can also then go to PowerBI.com click “Analyze in Excel”, and get an Excel Pivot table connected to the Power BI.com hosted data model.
From Power BI, click “Analyze in Excel” and get an Excel Pivot Table connected to your Power BI Dataset
This completes the round trip, bringing us full circle.
“Analyze in Excel” on Power BI brings us back to Excel
Wait a second? If I started off in Excel why the heck do I need to push it to Power BI to only then connect to it again using Excel.
“Avi you betrayed us! You swine! You let us waste our precious Power BI idea votes on this ridiculous feature.”
Before you start flaming me, hear me out. This was never about one person. This was never about the “author”, who creates the Excel / Power Pivot model. This is about the “consumers”. Yes, plural – consumers. 10s/100s/1000s of them. Power BI helps bring the data to everyone.
Excel => Power BI => Data Democracy!
With Analyze in Excel, once you publish your data model to Power BI, the users can consume it using a tool of their choice. And it’s not a single choice answer.Sometimes you may want a quick glance at the Power BI dashboard, other times you may want to work with the same data set in Excel.
Hundreds of users can consume the Power BI published data model,
using Power BI reports and dashboards or via Excel
Thanks to the Power BI Team, especially Aviv Ezrachi, Dany Hoter, Arina Hantsis and other Israel Development Center team members – for sharing this vision with the Power BI community and delivering (soon) this feature in Power BI. You can learn all about Analyze in Excel feature in the video below. You can also find all the videos from the MS Data Summit at their MS PowerBI YouTube channel.
MS Data Summit Sentiment Analysis
When I first heard about the Power BI desktop tool (then called Power BI designer) I felt very conflicted. Okay that’s an understatement. I felt, “Here we go, Microsoft team has done it again”. They have no idea that they already ruled the world of BI (via Excel). And here they go again building a new product, undermining an existing (very successful) one. I brought this up in discussion forums and was assured by the Power BI team, that they would not leave Excel behind. That Excel was part of the future vision. But talk is cheap, furthermore Microsoft hasn’t set very good precedents when it comes to matters such as this.
But it hit me at the MS Data Summit, and hit me hard. As wave after wave of messages washed over me. Excel and Power BI used in a sentence together, over and over again. Session after session talking about Excel and Power BI together, how they fit, how the complement each other. Excel and Power BI showed side-by-side on countless slides. Here’s my favorite one…
This Power BI + Excel slide featured prominently in many sessions,
including James Phillips’ Keynote (Click to watch)
I talk to my kids about trust and how it is hard to gain but easy to lose. My daughter once lied to us about having brushed her teeth (hence losing our trust), so now she needs a “witness” when she brushes her teeth (till she slowly regains our trust, over time). Microsoft has had a checkered past in this regard (to put it mildly). The change around how Power Pivot was bundled in Excel 2013, felt like a gut punch, and it still smarts. Hundreds of angry/confused comments on that post are testament to that.
Trust is hard to gain, but easy to lose
But the Microsoft Power BI team, under the leadership of James Phillips has toiled tirelessly, not just to build an amazing product but also to build a community, and slowly rebuild that trust.
Each of you would have to decide for yourself. But as for me, they have won my trust… and my loyalty.
Power On!
–Avi Singh
Get in touch with a P3 team member