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
- Original post (Oct 16): Does the Power Pivot Excel Add In Crash for You?
- Follow up (Nov 16): Power Pivot Crashes Update
- Second Follow up (Jun 17): Another Update from Seattle
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.
Matt, the simple way for Microsoft to address version compatibility of Vertipaq in Excel is to make the newer version available in online Excel and not in the on=premises Excel. they want to move people to 365 and this would be a subtle way to do it.
I love your work – simply awesome. I need some help with a project I am working on.
I have a Data Model with around 10 related tables loaded through Power Query. This model also has a number of Measures stored in different tables and Calculated columns. Using this model, I have a number of Power Pivot tables for visualization. As I would like to leverage this work for another project, I am not finding an easy/ elegant solution to copy all these objects in another Excel Workbook. I am able to export Power Queries and as connections and able to create a data model in the new workbook but finding it quite challenging to connect old pivot tables to use the newly created data model in the new Workbook. Is there any known method to achieve this?
This is a reasonably large topic for discussion beyond the scope of the comments in this blog. You have a couple of options for advice. You can engage me for commercial support http://xbi.com.au/matt or you could ask your question at http://community.powerbi.com
Matt,
Thanks for the post. It seems to be the only consistent recognition that something is really wrong with PowerPivot. It’s why I broke down and learned R. PowerBI is not, as far as I have seen, prone to the same frequency of crashes as PowerPivot. But PowerBI is not used in my organization, I wish it was. It’s unfortunate because PowerPivot is such a great idea. But like a lot of things in Excel, it seems like they just decided not to finish it. The cost of building a data model and losing it, is more expensive in time and frustration than learning R.
I keep hoping though, like earlier today when I built a simple model to verify some R code. But again I was disappointed that it still crashed. I even avoided common mistakes that I thought caused crashes, like re-naming already joined queries. I am running Windows 10 enterprise 64 bit, Office 365, 64 bit, Intel i7 with 16 G RAM.
I agree with you. Frankly, I don’t think you should hold out hope that this will change. Power Pivot started in Excel/SQL but then MS realised that it would be better as a stand alone product. In addition, MS never made any material revenue by bundling this technology in Excel. It does make revenue from Power BI. I don’t begrudge MS from wanting to make money from the great product. It can’t spend millions of dollars building software that doesn’t generate a return. So I think the future for Power Pivot is bleak and personally I don’t expect it will improve in a material way moving forward.
Wow, really thanks for this TAB explanation. I was unsuccessfully trying to figure out what the problem was, but now i know! 🙂
A question about your item, that I seen in a number of places:
“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,”
I’ve posted a much more detailled question about this on another book authors’ site, questioning if this has been verified as actually having happened. (As I saw that something similar happened in 2013.)
The problem is that at work I have SKU “Microsoft Office Standard 2019” with Excel’s Build Number “Version 1809 (Build 10827.20150 Click-to-Run)” and do not seem to have Power Pivot. At home, I have the Office 365 Personal, Version 2001 (Build12430.20264 Click-to-Run) that very clearly has Power Pivot.
Is there some sort of test suite that can show if we do or don’t have Power Pivot, and all of its functionality?
So far I haven’t seen any posts anywhere that show that Microsoft really did put the full Power Pivot in all SKUs, nor any that take them to task for not doing so.
Researching the SKUs, Build, and Version numbers, I came across some confusing material that makes it seem like my work version hasn’t yet gotten to the “Fixed a bug where Power Pivot did not appear in some builds/versions” listed in the changes for Excel. So it may be just that for those with Excel Standard 2019 versions and builds that are too old, it might not work. Those with more current versions likely have it work, hence no mass outrage, nor updated troubleshooting pages.
Hi Reed,
I assume you have already followed these steps and confirmed that Power Pivot isn’t included in your build, during your research, but thought I would add it here, just in case.
If you don’t see Power Pivot, (Power View or Power Map) options in Excel, you may simply need to enable them in the Options dialog box.
To do this:
1. Click File > Options.
2. Click the Ad-ins option on the navigation pane
3. At the bottom, you’ll see Manage [Excel Add-ins]
4. Change this to Manage [COM Add-ins], and click Go
5. You should then have a list of packaged add-ins, including Power Pivot for Excel
6. Check the box next to the add-ins you want, and click OK
7. Because Power Pivot is loaded during start up, you’ll then need to close Excel and relaunch.
You may also find this article interesting
Hi Reed. I spoke to Microsoft about this and I have confirmed the following:
* All SKUs of O365 and all SKUs of Office 2019 include Power Pivot.
* In Office 2013/2016 Pro Plus was required, but not in 2019 or O365.
* If Power Pivot isn’t working in one of the above approved SKUs, then it is a bug.
* Microsoft has an open bug #3966691 for Office 2019 Standard (Version 1809 Build 16.0.10827.20150). This is an internal Microsoft bug number and has no use outside of you communicating to Microsoft. If you wanted to log a support ticket you could quote that bug. But there is nothing to say the support person will be able to access that bug, as each team has their own bug register. But at least you could quote it and that *may* help expedite a resolution (not sure).
I hope that helps.
Hi Matt:
Just wanted to share my issue resolution:
Turns out I do have the latest version of Office 365, Version 1911, which is fairly recent.
The MS tech downgraded me to prior build, Version 1909 and problem was resolved.
I was advised to reinstall current version in ‘about 2-3 weeks’ when bugs in v. 1911 should be resolved.
‘There are always issues with a new build’ is what I was further advised.
I was encouraged that MS Support was aware of the issue and had a fix. Hopefully, future builds will not have the same effect.
Mike
Interesting. Thanks for sharing Mike.
Hi Matt:
I should note that the ‘fix’ worked for a few days and then the downgraded v. 1909 began to exhibit the same behavior as v. 1911. My suspicion is that it may have something to do with the fact that the file is being worked on in two different versions of Excel but, then, I have no way of knowing that. I don’t think it’s me, but I’m not sure about that either, as my experience with PowerPivot is limited. If I ever find out anything definitive, I’ll let you know.
Mike
Just wanted to add one final update . . . I did the test I should have done from the very beginning: I opened the file on another computer with Office 365 installed. (I had to extend my license to Office 365 Home which allows 5 users; I hadn’t done it as my ‘other’ PC is my son’s and he is in the third grade . . . but, it’s never too early to start, I suppose).
The file was opened as the first file in a new Excel session — and it opened and, subsequently, behaved perfectly normally.
So, the problem was specific to my other PC. Just for the record, that PC has an 8 year old ASUS P6X58D motherboard with the first generation 3.2 Ghz Intel i7 960 (4 core) processor and 16GB of RAM. Also, an AMD Radeon 6900 HD series video card. It was quite capable in its day.
I have absolutely no idea what is causing the problem on this system but it’s probably more trouble to find out than to update the system with some modern components.
Mike
Hi Matt:
Though I am a PP / PQ newbie (and have previously posted on your ‘Learn to Write Dax’ forum), I have been enjoying your book (Supercharge Excel) and have progressed enough to have built a straightforward 450K row PP file at work and am quite pleased thus far. At work (at the client site) I am in a 64 bit WIN 10 / Excel 2016 Pro Plus environment.
However, at home, I have a 64-bit WIN 10 install and a subscription to Office / Excel 365 and have the following issue:
My PP test file (based on the MS AdventureWorks db), when opened in a fresh Excel session (without any other files open) Excel instantly vanishes. It’s an instantaneous crash. Usually, the second time I try to open it, it opens and behaves normally. I have also noticed that if I already have other files open when I open the PP file, it will open normally. It seems only to happen when I have a fresh session of Excel and then open the file. I have also tried opening it from within the application, opening it from Windows Explorer (by double clicking) and opening it from the ‘Pinned’ file location in Excel. All 3 methods have the same result: the file will crash Excel on open when it is the first file opened in that session.
I did do a full Office Repair/re-install to no effect.
After some research (which led me here!) I followed Matt’s advice (as I loaded my data via PQ):
Power Query crashing Excel:
Data -> Get Data -> Query Options -> Data Load (Current Workbook) – clear all ticks.
Cleared the cache as well, but in a fresh session of Excel, the file will crash the application.
The only ‘fix’ I seem to have found is that, if I have another file open when I open my PP file for the first time, it opens and behaves normally.
So, I suppose it is a quirk that is fairly easy to live with.
I am posting this here in case MS engineers have not yet heard of this.
Of course, I know that it could be unique to my environment, but I run a pretty tight ship and surmise that it may be more universal than that.
Thanks very much.
Mike
I suggest you log a bug with Microsoft https://support.microsoft.com/en-gb You should quote your exact version number for Excel (it could be that it is quite old)
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?
I hadn’t heard about this. I suggest you log a ticket with Excel support, as this sound system like a big issue.
Hi Matt, thank you for your feedback, to be honest I have no idea how to log a ticket with the Excel Support. Of course I know support.office.com, but I never found a way to directly file a support ticket. Do you have a hint for me?
Yes, I mean support.microsoft.com
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…
Have you looked at dataflows as a way to share queries? https://exceleratorbi.com.au/moving-queries-from-power-bi-desktop-to-dataflows/
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.
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.
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.
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.
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
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.
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?
Good question about the updates. I will ask and let you know if I find something out. With your corruptions, is it VBA or Power Query that crashes? Have you looked at my other article about VBA and crashes. https://exceleratorbi.com.au/excel-keeps-crashing-check-your-vba-code/
MS is currently updating Power Query for Excel 2010/2013 download twice per year. This should continue into the future. I just checked the download site and the last update was 9th July 2018 https://www.microsoft.com/en-au/download/details.aspx?id=39379
Thanks for link to your post about the crashes. I hadn’t seen it before but will definitely try out some of your advice there. And thanks for the info about the new update policy for older Excels. Nice that we’re not abandoned yet, and the 6-month cadence should be fast enough for us slow-to-upgrade enterprises.
We have experienced the whole range of problems described here, the corruptions are a particular issue and occur randomly (without any VA involvement), the silent ones described by stevej are the worst, I’ve lost hours and hours of my life to those.
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.
” 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
I agree Nazim. I think it has its place, but a diminishing place at that.
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.
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.
Wow, I don’t think I have seen that before. Do you have a non-sensitive version of this that you can share along with instructions on how to reproduce?
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.