I think Power BI’s a great tool, but like most Power BI users I have a list of my own pet features that I would like to see implemented to make Power BI even greater. What I would most like to see addressed is the fact that, right now, Analyze In Excel only works with Excel on the desktop but not after you have published a workbook to Power BI. It seems crazy to me (and it’s very hard to explain to customers too) that Analyze In Excel lets you create reports in Excel using PivotTables and cube functions connected to a Power BI dataset, but when you publish your report to a Power BI workspace – so that the Excel workbook and the source data are both in Power BI – the reports stop working because Excel Online cannot connect back to Power BI.
There has been a post on the Power BI Ideas forum about this for some time, but recently I was talking to some guys from the Excel dev team and they told me that it’s actually something they, not the Power BI team, need to address. Therefore I created a post on the Excel UserVoice forum too:
Please vote for it! The more votes it gets, the more likely it is to be implemented quickly. Ken Puls managed to get a lot of votes for his idea to improve Power Query performance, and since that’s now in the process of being implemented it just goes to show that voting does influence what the Excel dev team works on.
Why is this important? In my opinion, Power BI is not a good ad-hoc data exploration tool and isn’t intended to be – its strengths lie elsewhere. However people do want to explore data stored in Power BI and an Excel PivotTable is the ideal way to do this (the Power BI matrix visual is very limited in comparison), and after you have found something interesting it’s only natural that you should want to share it. PivotTables aren’t the only thing Excel has to offer though: I’m a big fan of cube functions too, especially for creating financial reports, and Power BI has nothing remotely like them. Finally, don’t forget all those people who want to build reports in Excel because it’s Excel and that’s what they know. All in all getting this feature implemented would be a major boost for Power BI and broaden its range of capabilities.
Great postI find it very frustrating that there is no way to get a Excel with a connection to work in Power BI service, either with Power BI data model or SSAS. I’ll work on getting you some votes.
Sent from my Verizon, Samsung Galaxy smartphone
I’m always confused by the Analyze in Excel feature with Power BI. Is this a pro feature only? I have a pro license and my company has premium, but I’m gun shy to use analyze in excel because I don’t know if an excel file hitting the dataset in PBI Service will work if I share with a colleague who is a free Power BI user. I’ve also encountered the same issue as this article is bringing up and will vote for the idea. Huge believer in cube functions and the five part series you posted on leveraging some MDX functions within the cube functions is required reading for any financial analyst.
Hmm, I’m pretty sure it should work if you’re using Premium but I don’t have access to a Premium capacity to test this right now.
Voted up!!
Sent from my iPhone
>
Hi Chriss,
What about the office version to use “analize in excel”? Should it be the same version that contains power pivot or can be any version of office (home, student, etc) ??
Any recent version should be fine. “Analyze in Excel” does not use Power Pivot/the Excel Data Model.
It should work with any modern Windows version of Excel (ie Excel Office 265/2016/2013 and 2010 I think). There’s no need to have Power Pivot because this does not use Power Pivot.
Do you know of any alternatives to publish Excel files with a connection to SSAS on Azure?