A further (final?) update about Power Pivot

This is the longest running blog post series I have on my website.  Why does Power Pivot Crash so often and what can I do about it?  I have three previous articles that you can read in the links below

What Sparked My Re-interest?

Recently I was helping a client who is using Power Pivot for Excel 2013, and this re-ignited the topic for me.  I stopped training students using Power Pivot more than 18 months ago.  The reason I stopped was that I found Power Pivot for Excel far too unreliable to use as a tool to teach new users. The problem is that when you are new to a technology you have enough problems to deal with, without having to deal with the software crashing.  I found that Power Pivot was just not reliable enough to be used to teach new users.

What has Changed (Apparently)?

When I was helping my client, I found that the Measure dialog in Excel 2016 was really bad.  What do I mean by “bad”?  I was typing measures in the dialog and it just wasn’t working “right”.  I would type part of a function name, eg “CALCU” and press tab.  The way Intellisense is supposed to work is that pressing TAB will accept the function from the list and then I can continue to write the rest of the measure. Instead pressing tab moves the focus to the next object in the dialog – eg the OK button.

Oh shoot I thought!  Power Pivot is just as bad as it has been for years.  What the heck!?  Time to contact Howie to find out what is going on – and I am glad I did.

The Truth About What Has Changed

I am fortunate to have met some fabulous people at Microsoft.  There are so many people focused on making things better that it is remarkable.  Howie was charged with the unenviable task of leading a team of developers to fix the bugs in Power Pivot for Excel.  In fact before Howie and the team took over the code, Power Pivot was past back and forth between quite a few teams and no one really took ownership of the problems.   That changed when Howie and team took it on and they set out to fix the issues.  There were literally hundreds of bugs in the code, and they have worked through the list, one at a time, until the list was all but gone.

But Why Then Are you Complaining about the UI Matt?

What I learnt while chatting to Howie is that most of the real issues have been fixed.  The issue I described above is a strange nuance in the code – let me explain. Power Pivot for Excel (2016) has 2 separate editor windows for Excel.  If you click on Power Pivot\Measures\New Measures you will get one UI experience.  If you right click on a table and select “Add Measure/Edit Measure”, you get a different measure editing dialog box all together.  This is a legacy hang over from the time when Power Pivot for Excel was only available in the premium SKUs. Thankfully Microsoft decided recently to make Power Pivot available to all Excel SKUs, and hence this second editor dialog is technically no longer required – but it still exists.  The former (Power Pivot\Measures\New Measure) is a stable, robust part of the UI.  The other editor (right click on a measure in the fields list) is buggy and may not be fixed.

Key Learning:  Don’t right click to add or edit measures when using Power Pivot for Excel.

Compatibility Differences

As a side note, the “engine” (called Vertipaq) inside Power Pivot for Excel is the same engine inside Power BI Desktop, PowerBI.com and also SQL Server Analysis Services Tabular.  Actually, I say “same” but the reality is there are version differences between them all, but at the core, they are the same technology (Vertipaq).  If you learn data modelling in Power BI, you are also learning data modelling in SSAS Tabular and also data modelling in Power Pivot for Excel (even though there are some version differences).    Power Pivot is a much older version of Vertipaq – deliberately so.  Power Pivot for Excel is part of the Office Suite and Microsoft has a strategy of maintaining as much version compatibility as possible between different versions of Office.  At least in part this is why Microsoft is not currently pushing the newer Power BI updates (eg bi-directional relationships) into Power Pivot for Excel.  Power BI Desktop on the other hand is a new product that is not constrained by the Office compatibility issue.  If you want to use Power BI Desktop and want it to be compatible, you need to use the latest version – it is as simple as that.

It is not possible to say if and when Microsoft will bring Power Pivot for Excel up to a newer release of the technology.  I wouldn’t even speculate on this one as I think it depends on so many other things that anything could happen.  The only thing I would speculate on is that I think nothing will happen anytime soon.

Let me know what you think

Please let me know what you think about your version of Power Pivot.  Is it robust?  If not, what issues do you have?  Please post your version and build number (Help, About) so it is clear which version you have.  Just saying Excel 2016 is not enough as there are may different builds and versions with the same product name.

Share?

Comments

  1. I do not use Power Pivot so much these days and I don’t have much crashing experience with it. I do, however, suffer from frequent crashes with Power Query. I rack my brains to try to work out what it is I am doing that causes the crashes but it crashes with a big file, a big import, a small file, a small import. The last crash, three or four days ago, was caused by Appending queries.

    I have mentioned here before that this behaviour once cost me half of my presenter’s fee for one course I was running when the crashes were so bad that, in spite of testing my file in the morning and then rebooting more than once when the crash happened during my training session, I had to abandon the final session of the course. Of course, as soon as I got back to my hotel room, problem solved.

    • Duncan, I’d recommend to check “Background Data” setting for the particular workbook. For me it is usually a reason why Power Query crashing Excel.
      Data -> Get Data -> Query Options -> Data Load (Current Workbook) – clear all ticks.
      In addition, I’d switch off “refresh in background” for each query loaded to the worksheet.

  2. Actually, with the advent of PBI, Power Pivot for excel has become sort of redundant. This may be one reason why microsoft is not no keen on improving Power Pivot. In fact, I have never used Power Pivot for the last three years and probably never feel the need to do so.

    • PowerTools in Excel or via PowerBI Desktop is a usecase difference, or a granularity case.
      I see PowerBI Desktop a great tool for the big picture presentations, reports and dashboards.
      In Excel, the tools are better suited for detail “show me the one transaction” situations.
      My work environment doesn’t really support PowerBI as a publishable form, so I’m stuck on the Excel side of things. Being able to use the DataModel and DAX makes many task much simpler and less time consuming as projects get scaled above 60k records.
      And in some cases possible.
      I hope Microsoft will see that Excel will be the Gateway for many to reach PowerBI Desktop and letting issues remain in the Excel will only hinder their goals for PowerBI.

  3. ” I think it has its place, but a diminishing place at that.”
    @Matt
    Once Power BI Custom Visuals become available in Monthly channel/Semi-annual channel (currently available on in Insider release) it is Power BI Desktop that will become irrelevant.
    Power BI exists because of Excel not the other way round.
    Unfortunately the Program Manager(s) in the Excel Team are not strong enough to drive the vision or else they would have never allowed the Vertipaq and the Mashup engines to lag so far behind the PBI Desktop versions of those engines
    Backward compatibility is just a poor excuse that Microsoft uses whenever it is convenient to them
    They didnt care about backward compatibility when they changed the Menu based UI to the Ribbon did they ? – Then it was called as a “Bold Move”, “Modern UI” etc etc.
    There are hundreds of such cases.
    So it just about the Dog waging the tail or the tail wagging the Dog

  4. Hi Matt, I’m still a frequent Power Pivot user as we do a lot of Excel-based reports in our company. Excel 2016 32-bit, monthly channel, so 1811 build – developer’s software. Consumers mainly browse reports through Excel Online as they are published on SharePoint. What can I say? Not 100% reliable, totally true. Working with Excel have to be very careful: don’t open unnecessary files, keep backup files, save version copies etc. I don’t rely on SharePoint / OneDrive versioning because had bad experience, couldn’t restore hours of work couple of times. Lack of bi-directional relationships is an issue, yes; lack of some functions, e.g. TREATAS, is an issue, true; crashing developed a paranoid habit of frequent saving… Sometimes active file with Data Model gets corrupted, e.g. new PQ query shows it has loaded data to the data model, but new table doesn’t appear there, so have to close the file. And sometimes, Windows UI (taskbar, basically) shows no Excel files open, however, Excel process still running in the memory. If open a new file – that process (with corrupted memory) will be used to open a file instead of a new clean process… so, rule of thumb (another paranoid habit) – check no Excel processes left (or simple .bat file with taskkill /im excel.exe -f).
    Despite experiencing all mentioned issues we still use Excel+PowerPivot solutions and build new ones.
    I still see quite many cases (and reasons “why”) when Excel file empowered with the Data Model is more convenient solution than Power BI (for both – developer and consumer, and potential future developer/supporter). However, this is just my situation; generally, it fully depends on the situation with the software availability in a company.

  5. I’ve done a fair amount of work using Power Query and VBA on Excel 2013 (client’s deployed version of Office). Mostly, these are ETL-type processes that clean and combine large-ish flat files.

    But during development Excel crashes a lot, often corrupting the workbook in the process. Sometimes it silently corrupts the workbook which I won’t discover until after a save/close/re-open. So I have learned to stop, save, close, and backup the workbook constantly. In fact I probably would have given up on the tech by now, except that I really like M, and once the code is complete, the workbook generally works reliably in production for the desired use case. (dev is on 64-bit Excel, deployment is to 32-bit.)

    Also I’ve noticed that there have been no updates to the downloadable Excel add-in for PQ since July 2017. Is support for Power Query and Power Pivot for older Excels now discontinued?

  6. I almost never user PowerPivot anymore (with one caveat), though I use Power Query in Excel all the time. Generally if I’m doing the kind of analysis where I’d want to use PowerPivot, I want all the nice features of Power BI.

    The one caveat to this is I’ll do basic pivot tables in Excel pretty frequently, and I always add them to the data model just to get that sweet, sweet Distinct Count option on my pivot tables. I don’t consider this really using PowerPivot (no measures, no multiple tables, no calculated columns), but I did want to throw it out there.

  7. Hi Matt,
    I bring in my data into Excel using Power Query (I love Power Query) and then use the power of PP for my reports. I am learning how to use the mysterious “M”(Cube formulas) to get away from some of the constraints of PP pivots. It is frustrating if a client is on an older version of Excel where PP or PQ needs to be downloaded. I even ran across one of the hosting companies that had claimed to have Excel 2016 but their version of PQ was not full functioning.. Very frustrating. I had to rewrite my queries using PP and the file ended up being twice the size.. Am moving more of my reporting to Power BI as business owners like the visuals.

    BTW… I have your new book and devoured it.. Thank you… I come from the Excel Analyst side of the house, so you were really speaking my language.

    • Hi Fran, long time, no see. you mention “M” and Cube Formulas in the same point – these are different things, right? Or have I misunderstood something?
      Regarding Excel 2016, there are different release updates – you can check which they have via File\Account and check the version in the About Excel section. There are a lot of different versions

  8. I recently upgraded from Excel 2010 (64-bit version) to Excel 2016 (32-bit version) at work and I was excited to be using a newer version of Excel, but I soon experienced frequent Excel crashes. Those crashes were not solely due to 32-bit vs 64-bit issues, because I also experienced similar crashes with my 64-bit Excel 2016 on my home PC that has a decent 32 GB RAM. Regarding the performance of Excel 2016, I was expecting the newer version to perform better both in terms of speed and robustness, so I was rather disappointed with the frequent crashes. For me, calculation speed and reliability are the most important features for software, so rather than incorporating additional bells and whistles which really don’t add any value, I’d greatly appreciate it if the software developers focused on these aspects when releasing a newer version of Excel. Also, when I tried to upgrade my data models from Excel 2010 to Excel 2016, I experienced errors for some files, and while some of the files could be upgraded successfully after deleting the MDX sets, there were a couple of files, which I couldn’t upgrade after googling all of the available information, and I ended up rebuilding the data models from scratch which consumed a lot of my time. On the other hand, I was impressed with the speed and reliability of Power BI compared to Excel, especially with the quick visual slicer response time. Nevertheless it is a fact that we cannot do everything in Power BI alone, as it is designed for presenting the big picture and we still need to rely heavily on Excel when undertaking the usual types of analysis that are required in an accounting department.

  9. Hi Matt
    I will tray telling you about the experience of over 200 small and medium customers on at least triple that number of computers that use Power Pivot to view their data (Mostly Financial/Budget/Cost Accounting Data), after it was prospected using a separate software called Contiki (contiki.co.il not to be mistaken with contiki.com).
    When using Contiki, there is no place to use Power Query, as it deals in a slightly different approach (and to my opinion much more stable) with the ETL side of business.
    Contiki’s customers use all kind of Excel 2016 and 2019 versions and some still use Excel 2013.
    We hardly any more encounter any crashes when viewing data.
    When refreshing Data we use a code by Contiki, using a trick I humbly admit I learned from you (Matt) a long time ago, to add a task: “taskkill /f /im excel.exe” as often as possible.
    This made a huge difference, making the refreshing process stable.
    Regarding crashes when building new models or changing existing ones; We learned how to be careful, as mentioned up above.
    Power BI is Better then Power Pivot in many ways, but is also different.
    To my opinion, when the task is to get the data in to complicated and detailed reports, Pivot Tables with Power Pivot and DAX in the back, many times do a better job.
    There is also a huge difference in the distributing the data: Some time sharing, sending or publishing an Excel file suits users better than using the PBI cloud.

  10. I mainly use Excel rather than PowerBI. 32 GB RAM, Office 365 64-bit

    It crashes or hangs at least once a week, but I’ve got a ton of convoluted queries and measures that I execute on millions of data rows, so I’m not sure if PowerBI would fix that. For sure I can only write 2 bad measures before I know I have to close and restart.

    a) Our organization doesn’t have a strong PowerBI capability so there’s nowhere to publish
    b) PowerBI is for “finished” presentations, not nitty-gritty development. From one week to the next I don’t know what new data slices I’m going to want.
    c) I have 20 years of macros and formats to do more with my work in charts and presentation than PowerBI offers.

    If the organization fixes (a) then I would certainly consider moving more work to PowerBI, but I still like being able to move pivot table results to a clean Excel sheet and do some fast manipulations.

  11. I’m using Office 365 Pro Plus, and currently have Version 1905 Build 11231.20080. And I’m running this on an MS Laptop Pro i7-766OU with a 2.5 GZ processor, 16 GB of RAM. So I’ve got a recent version running on a powerful machine.

    I’m getting all sorts of crazy behaviour from a combination of PowerPivot and OneDrive. To the point that I don’t trust either. So even though I’ve got autosave turned on, I save a new version regularly. I’ve found that sometimes old versions don’t restore. And I’ve found after restarting my PC, opening Excel, and clicking a file name in the recent files that the file opens for a millisecond, then Excel simply closes, with no error messages. One second the file is opening, the next second Excel has disappeared.

    This isn’t a big file, although it has some pretty nifty transformations using PowerQuery, and some pretty interesting DAX.

    Long story short…I only persevere with Excel because even with the crappy behaviour I’m experiencing, I’d rather use this new functionality than go back to pre PowerPivot days. (Yes, I could use PowerBI which is much more stable, but the PowerBI graphical front end is nowhere near as customizable as the Excel charts objects model).

    Happy to share the file with MS or anyone: It’s public data I’m crunching so nothing confidential.

    I’m ONLY using the editor available from the PowerPivot tab, but on the file I’m working on at the moment, the measure editor often displays strange lines that look like the pipe character | instead of text as I type.

  12. I use both Power Pivot and Power BI and Excel indeed sometimes crashes. Unfotunately I’m stuck with the 32 bit version though so that might be a cause. Personally I don’t think Power Pivot is going anywhere as the data model is now standard issue in Excel. I really hope Microsoft updates it though so the 365 version is on par with Power BI (and maybe give it the same name in both environments as well like they finally did with Power Query). The ideal would be to be able to move queries and the data model back and forth between Excel/Power BI/Tabular. Which brings me to another observation: the Tabular model editing experience in Visual Studio could really use an update as well…

  13. Hi Matt,
    I`m wondering that I cannot open anymore my “old” PowerPivot Datamodels (from the Oct/Nov 2018 Excel version) in the current versions 1904 and 1905 (both 64-bit). Opening an Excel file with a PowerPivot Datamodel included leads to the start of Excel but it is not possible to click somewhere in the Excel Window. On the first click Excel crashes and closes the window immediately. I tested several files and I´m quite sure that the Power Pivot Datamodel ist the reason for the crashes -> does someone know this problem and even a solution?

Leave a Reply