My favourite Power BI announcement at the Microsoft Business Applications Summit was, without a doubt, that Excel PivotTables connected to Power BI datasets will very soon work in the browser and not just on the desktop. This is something I have wanted for a long time, way before I joined Microsoft, so this is a feature I have a personal interest in. However I also think it’s an incredibly important step forward for Power BI in general and in this post I’ll outline the reasons why.
Before we carry on please make sure you read this post on the Excel blog which has more details on all of the new Power BI/Excel integration features that are being released. Quick summary: if you’re reading this in late May 2021 you probably won’t have all of this functionality available in your tenant yet but it is coming very soon.
So why exactly am I excited?
It makes Excel a third option for building Power BI reports
Up to now, if you wanted to build Power BI reports and share them with other people online you had two choices: regular Power BI reports and paginated reports. Now Excel gives you a third option: you can upload Power BI-connected Excel workbooks to a Power BI workspace, make them available via a Power BI app, and not only will they be fully interactive but the data in them will also update automatically when the data in your dataset updates.
PivotTables are the best way to explore Power BI data
Why do we need Excel as an option for building reports on data stored in Power BI? The first reason is data exploration. Excel PivotTables are a much better way to explore your data than any other method in Power BI, in my opinion. Why try to recreate an Excel PivotTable using a matrix visual in a regular Power BI report when you can give your users the real thing?
Cube functions also now work in the browser – and they make it easy to design financial reports
The Excel cube functions (CubeMember, CubeValue etc) are, I think, the best-kept feature in Excel. While PivotTables are great for exploring data they aren’t always so great when you want to build highly-formatted reports. The Excel cube functions make it easy to bind individual cells in a worksheet to individual values in your dataset and because they’re just like any other Excel function they allow you to use all of Excel’s formatting and charting functionality. This then makes it possible to build certain types of report, such as financial reports, much more easily. If you want to learn more about them check out this video from Peter Myers – it shows how to use them with Analysis Services but they work just the same when connected to a Power BI dataset.
Organisational data types make it easy to access Power BI data
While the Excel cube functions are very powerful they are also somewhat difficult to use and sometimes suffer from performance problems. The new organisational data types in Excel do something very similar and while they don’t yet have all the features you need to build complex reports they are also a lot easier to understand for most business users.
Excel formulas are easier than DAX for a many calculations
Everyone knows DAX can be hard sometimes. However, once you’ve got the data you need from your dataset into Excel using a PivotTable, cube functions or organisational data types you can then do your own calculations on that data using regular Excel formulas. This not only allows business users to add their own calculations easily but for BI professionals it could be the case that an Excel formula is easier to write and faster to execute than the equivalent DAX.
Excel can visualise data in ways that Power BI can’t
Excel is a very mature data visualisation tool and it has some types of chart and some formatting options that aren’t (yet) available in Power BI’s native visuals. One example that springs to mind is that you can add error bars to a bar chart in Excel; another is sparklines, although they are coming to Power BI later this year.
Power Pivot reports will also work in the browser
Even if you don’t have a Power BI pro licence, if you have a commercial version of Excel you’ll have Power Pivot and the Excel Data Model. And guess what, Power Pivot reports also now work in the browser!
Collaborate in real-time with your colleagues in Excel Online
With Excel reports connected to Power BI stored in OneDrive for Business or a SharePoint document library you get great features for collaboration and co-authoring, so you and your colleagues can analyse data together even if you’re not in the same room.
There’s a lot of other cool stuff happening in Excel right now
The Excel team are on a hot streak at the moment: dynamic arrays, LAMBDAs, LET, the beginnings of Power Query on the Mac and lots more cool new stuff has been delivered recently. If you’re only familiar with the Excel features you learned on a course 20 years ago you’re missing out on some really powerful functionality for data analysis.
Everyone knows Excel!
Last of all, it goes without saying that Excel is by far the most popular tool for working with data in the world. Everyone has it, everyone knows it and everyone wants to use it. As Power BI people we all know how difficult it is to persuade our users to abandon their old Excel habits, so why not meet them halfway? Storing data in a Power BI dataset solves many of the problems of using Excel as a reporting tool: no more manual exports, old-of-date data or multiple versions of the truth. Using Excel to build reports on top of a Power BI dataset may be much easier to learn and accept for many business users – at least at first – than learning how to build reports in Power BI Desktop.
I know many people that discount Excel and think …. “oh we could do that in Power BI” yet there is so much for what i call quick ad hoc stuff where Excel and Pivot tables are really easier and faster for exploring the data and some quick and dirty reports. I am so glad to see it, but wish people would stop thinking if it is in Excel it is not as good.
touche Mr. Jones. The manipulations I see required in Power BI to avoid errors are turning out to be as complicated as I would encounter in an Excel application
Hi Chris, thanks for this detailed post. One question, this feature only works against Power BI dataset or would also work against azure analysis services dataset?
No, Excel Online won’t be able to connect to AAS models unfortunately
This would be a gamechanger for us. But I suspect they want to give more distinction to PBI than AAS.
When can we expect this to be implemented?
Excel Organization data types from Power BI is limited to Power Bi pro licences, even in a premium environment. But core target of this feature are data consumers, not Power BI builders. It won’t solve vlookup hell with current limitations.
Agree. This capability seemed very exciting, until I realized most users, even on Premium capacity, can’t use it. Why would we even bother implementing it on our enterprise datasets?
The other mystery to me is why can only Pro users click the Get Data > From Power BI button?
Part of me is excited that these two products, which obviously go together, are finally getting tighter integration. But, another part is worried that licensing is going to make it more confusing than useful.
My end users certainly do love Excel. I am wondering if this means that Microsoft is essentially phasing out it’s support of SSAS (particularly Tabular on-premises).
@Chris – do you know the “real” reason for the below game chaining idea to get dropped almost immediately after it was announced
https://powerbi.microsoft.com/en-us/blog/excel-announces-new-data-visualization-capabilities-with-power-bi-custom-visuals/
Cheers
Sam
I don’t know, sorry, but I can pretty much guarantee that the reason will be something boring like security or lack of development resources.
@ Chris – My guess is the fear of Excel among other teams in MS.
Imagine a strong leadership in the Excel team had ensured parity in the
a) Mashup engine (all 153 connectors, Direct Query etc.
b) Vertipaq engine (all DAX Functions + Model enhancements)
c) Re-imagined Visualization Layer (like envisaged in the link above)
Do you think customers would pay 10$ /user / month ?
It is imperative that the Power Components in Excel remain at least a generation behind that in PBID for Power BI to sell.
All this “Excel and Power BI – Better Together ” sounds good in conferences – The reality is something quite different.
Cheers
Sam
If you go back five years, the Microsoft BI strategy was centred around Excel. It wasn’t a success for a variety of reasons, which is why Power BI exists as a separate product now. I promise there is no conspiracy to keep Excel behind; the reality is a lot more boring.
Hey Chris, first of all, awesome blog.
Agreed 100%, I’m super excited to have this feature. Do you have an idea when this will be launched?
I’m specially interested in refreshing my SQL connections automatically every day.
I’d like to be able to get a live connection to the data in a specific Power BI report. That isn’t possible is it? I’ve already done a lot of work in Power BI to generate that report and I don’t want to replicate it in Excel, but it would be very useful to use the report data in a PivotTable.
That is exactly what is possible – you can already get the equivalent of a live connection from an Excel PivotTable to a Power BI dataset on the desktop, and it will soon work in the browser too.
PowerBI / Excel : I am really happy that Excel finally connects with PowerBI, and preformatted Excel Pivot Tables will now work with PowerBI Datasets. But isnt it possible for Excel PT to be ‘linked’ to PowerBI ? For example cant the PowerBI slicers sync with Excel pivot fields ? Isnt it possible for Excel PT to be a core visual, instead of the very boring powerbi matrix visual ?
Are there any plans to have Excel pivot tables communicate in DAX instead of MDX?
Poor translation seem to cause frequent memory error in reports that worked fine against MD cubes.
No, sorry
Good article.
I found it interesting that you identified charts in Excel that cannot be created in PBI. That is one of the features I thought PBI did better than Excel … .
Chris – have you been able to get this to work?
I have access to several tenants and have yet to get it to work.
Here’s my setup… perhaps I’m doing something wrong…
– PBI dataset (import mode) published to premium workspace
– excel file on laptop created via analyze in excel
– open excel file on laptop, publish (via upload option) back to same premium workspace
on all (5) tenants, it get the same error when trying to change the pivot table: “we were unable to refresh one or more data connections in this workbook. the folowing connections failed to refresh pbiazure://api.powerbi.com Model”
found this video… https://t.co/o1QxXlGTsD
i missed the part where the excel file needed to be hosted on sharepoint/onedrive :facepalm:
Yes, sorry – I didn’t make that clear in the post because it wasn’t decided whether this would be required when the feature was released.
Hi! Thank you, this is really great feature!
I created a Excel file with tabular model connection and I saved this file/report to One Drive Business. I published this workbook to my workspace (app.powerbi.com). I tried to use slicers/refresh data and now I get this error:
“We were unable to refresh one or more data connections in this workbook.
The following connections failed to refresh: my_tabular_model_name ”
Tabular model connection works fine when I open the workbook saved to One Drive Business. Is there something I need to do in app/workspace? Create dataset or something like that?
When you say “Tabular Model” do you mean SSAS or AAS? Only connections to Power BI datasets are supported in Excel Online.
I mean SSAS. I thought Excel online could use ssas-connections.
No, it can’t, sorry – only connections to Power BI datasets at the moment.
Ok, thank you, and I found out that “refresh and interactivity of Power BI datasets with a live connection to Analysis Services is not supported” -> I have to publish my excel workbook to SQL Server Reporting Services and my Power BI report has link to it..
I can connect Excel Online to my Power BI datasets. However, German number formats will be returned as text because the numbers returned have the wrong English decimal separator. 😥 Maybe, you can forward this.
Hi Chris
We have a setup where we have SSAS On-Premise (Due to EU law), but use PowerBI.com to hold the PBI reports which then queries live through the gateway to the On-Premise OLAP model. Everything is fine, except that when choosing “Analyse in Excel Online” the Pivot is not working. Having this feature working would finally “close the loop” of a much needed feature. Do you know if any effort is made to make this work ?
I’m not sure -I will try to find out
Thanks – I’ve been missing this feature ever since having customers using it in Excel Online in Sharepoint 2010 🙂
The closest i’ve found is how to enable it for Office Online Server but it doesn’t help much for the Office 365/PowerBI.com end user experience.
https://docs.microsoft.com/en-us/officeonlineserver/data-authentication-for-excel-online-in-office-online-server
The only thing i have found is how to configure it for Office Online Server, but it doesn’t help in regards to Office 365 and PowerBI.com.
https://docs.microsoft.com/en-us/officeonlineserver/data-authentication-for-excel-online-in-office-online-server