Ten Reasons Why I’m Excited About The New Power BI/Excel Integration Features

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.

26 responses

  1. 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

  2. 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?

  3. 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.

  4. 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).

    • 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.

  5. 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.

  6. 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.

  7. 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 ?

  8. 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.

  9. Pingback: Excel Roundup 2021-06-24 – Contextures Blog

  10. Pingback: Chris Webb's BI Blog: Generating Excel Reports Using Power BI, Cube Formulas, Office Scripts And Power Automate Chris Webb's BI Blog

  11. Pingback: Power BI ♥ Excel – Stark Analytics

  12. 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 … .

  13. 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”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: