When I was at the Microsoft Data Insights Summit last week I took the opportunity to catch up with Howie Dickerman to get an update on how the bug fixing with Power Pivot for Excel is coming along. I talked about this in my original blog here and then my previous update here. And the good news is the work is going very well. The summary of what Howie told me is as follows:
- There have been more than 200 bugs fixed to date particularly those that occur from the Edit Measures dialog. This is exactly where most of the crashes I see occur so that is good news.
- All known errors in the editing dialog are believed to be now fixed. You can never say never of course, but this is the current view
- All known bugs where Excel seems to hang while waiting for Power Pivot (or vice versa) seem to be fixed.
- Definitely not everything is fixed, but it is a lot better.
- You need to get a version of Excel 2016 O365 that has a build date of April 2017 or greater to get the fixes (more on that below)
- Work on the MSI version (non- O365) will start “soon”. But please don’t hold your breath on this – these things take time.
Howie really cares about this stuff and he asked me to keep him updated on what people find as they deploy the newer releases of the product, so I would love you to comment below on your experiences AFTER you have installed a >= April 2017 version.
How to get the Updates Faster
In short
- Get Excel 2016 Office 365
- Switch to Current Channel if you are on the Deferred channel (like me).
Current, Deferred, What?!
OK, I admit I had never heard for the Current Channel or the Deferred Channel. Each customer has a choice as to how often their software is updated and also how quickly the updates are deployed. As you could probably guess, the Deferred Channel gets the slowest updates and fixes and also is updated less frequently. This Channel is designed for conservative IT departments and/or for companies that need absolute stability in their environment for whatever reason. On the other hand the Current Channel pushes out fixes pretty frequently (as often as monthly on occasion) so you get the latest fixes as soon as it makes sense for Microsoft to deploy. There is also a Fast Track channel where you can get bleeding edge updates, but you should use that with care! This channel is generally not advisable if you have any responsibility to deliver reliable material in full on time – things can and will go wrong. This channel is really designed for people that want to test new features etc.
I have my own o365 tenant and I am the Administrator (read I don’t have to ask anyone in IT for permission to do what I want 🙂 ). I was actually surprised to find out that I am on the Deferred Channel for updates. I certainly didn’t ask for this so my assumption is that this must be the default (not sure). Anyway, it is not hard to switch from the Deferred Channel to the Current Channel. Read about that further down the page.
Which Channel Am I On?
You can check which Channel you are on by going into File\Account. You can see below that I am on the Deferred Channel
The version 1609 shown above means the code was written before Sept 2016 (eg YYMM is the deployed year/month)
How to Change Channel
I just downloaded and installed the “easy fix” option at this link here. https://support.microsoft.com/en-us/help/3185078/how-to-switch-from-deferred-channel-to-current-channel-for-the-office-365-suite and ran the software. This successfully changed the Chanel for me for my entire deployed copy of MS Office. After I deployed the new version I immediately saw lots of changes to Excel and also Outlook. Mainly smallish cosmetic changes, but noticeable all the same. Nothing really relevant to this article though.
After installing the fix, I saw that I was on the current channel and the version is 1705 (meaning it contains code developed up until April 2017).
What About Me?
I hear you say “what about ….”? If this is you
- Excel 2010
- Excel 2013
- Any Excel that is not O365
- Deferred Channel O365 where you have no control (eg IT says “too bad”).
If that is you, then the bad news is it will take longer for the fixes to flow to you. It is not possible to say how much longer unfortunately, but I wouldn’t be holding my breath for anything in the next 3 months. Unfortunately if you want the benefits of subscription based licencing, you have to buy the subscription. And if you (or someone who calls the shots) wants the benefits of a slowly changing environment, then you have to accept that change will come slowly. And if you are running a version of the software that is 3 – 7 years old, then you have to accept that this will come at a cost and you can’t have the latest and the best of everything.
Issues Already Discovered
There are 2 things I have discovered already about the new build.
Auto Add Measure to Pivot Table
It has been a feature of Power Pivot from day 1, that if you have a pivot table selected when you write a measure, that measure automatically gets added to the Pivot Table. I noted immediately that this feature no longer works. I contacted Howie and he tells me that this was turned off deliberately to try to drive some consistency in the process. However since deploying the change there has been significant “feedback” and the team has decided to turn this back on in a future release.
Formulas Not Being Saved
I have heard from Austin Senseman at PowerPivotPro.com that he has experienced problems when adding a new measure in the formula dialog but the formula can’t be saved. He has to copy the formula, close the dialog, then open the dialog and paste the formula.
Leave Your Bug Experiences Below
Please leave your comments below of your experiences after you upgrade to an April 2017 build. I would love to hear the good and bad, and I will definitely pass your comments on to Howie – he wants to know so the team can make the product better.
Relevant Articles
This is the last post in a series of articles. You can read the others in this series from the following links.
Hi Matt, It’s been a while since we had an update on the state of the project to make PowerPivot less twitchy during development. I’m using 365 ProPlus and a 16 months after your last update the process still hangs up or fails to save. Or worse, saves, but then will not open.
On thing I have found in my version has been if the new formula is giving an error and you believe in your formula, try reloading one of the tables. This does something to the workbook and has worked 100% for me on this issue and occasionally has worked with other issues during development. It’s become my first step in troubleshooting.
Now, about fixing the rest of the issues.
I do have a question about file size for 365 ProPlus. Anyone notice an increase in issues after reaching a particular workbook size and what is that magic size?
I’ve got one that has two CROSSFILTER() functions in two different measures and it keeps giving a “An issue with the Data Model is preventing Microsoft Excel from opening this workbook. Try restarting Microsoft Excel” after I edit those measures, but usually editing a different measure will clean it up. I guess, as it refreshes and doesn’t give that error.
My “fix” thus far as been to not edit those measures anymore, but use new measures that refer to those measures. Makes for more measures than I want.
This is with build 1809 on Fast ring in late August 2018. So whatever the issues they think they have fixed, it isn’t fixed. 🙁
Hi Matt, I was horrified to start losing data models for Excel 2016 Power Pivot this week after I upgraded to 64 bit Excel 2016 msi. Ran across this series of articles in researching the issue – and have been helped by many of your other articles in the past by the way – thank you! I’ve lost many hours of work this week over this. If I can’t go to Office 365, should I downgrade to Excel 2013, or try to use every workaround and precaution “in the book” until Microsoft trickles the fixes down to msi? Right now besides all of the other recommendations I’m literally stopping and saving 3 identical copies with version names of the workbook every 1/2 hour so that if the current one corrupts I have not lost too much time.
I wish I could be more optimistic about the approach and the permanent fix, but unfortunately I am not optimistic. Personally I would not downgrade to 2013 because 2016 is a better UI and has a more up to date version of Power Pivot. The solution is to incrementally save your workbook (unfortunately) . I wrote some VBA code to make this easier way back when Power Query with Power Pivot was an issue. You may like to use that code to help you. https://exceleratorbi.com.au/create-incremental-backups-when-using-power-query/
Following excelisfun excel magic trick 1398 on youtube, when I tried to add the DAX formula
=CEILING(fQuantity[Quantity],50) Video position 8:15/41:13, my workbook freezes.
Microsoft tech support confirmed the error and said they would work on it. My work-around is as follows:
=Floor(fQuantity[Quantity],50)
LowerRegularIncrement
=floor(fQuantity[Quantity],VALUES(vIncrement[Increment]))
UpperRegularIncrement
=([LowerRegularIncrement]+VALUES(vIncrement[Increment]))
Of course, you can do one better and move from Slow ring to Fast Ring! You will generally get updates one a week. You are bleeding edge, but see features, fixes, AND BUGS, before anyone else outside of MS campus. Go to the page linked below and scroll down to the “Install even earlier preview builds of Office 2016 (Office Insider Fast builds”) section. The first time it does it, it will render office unusable until the full 800 or so MB downloads. After that, it downloads in the backgrounds and updates take less than a minute.
https://support.office.com/en-us/article/How-Office-365-commercial-customers-can-get-early-access-to-new-Office-2016-features-4dd8ba40-73c0-4468-b778-c7b744d03ead?ui=en-US&rs=en-US&ad=US
FYI. If you are in a corporate environment and are deferred and you want to update to Current without the administrator making any changes, you can run a .bat script to check for Current updates (I run it once a week). This one is mine for 64bit Office.
setlocal
reg query HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\Configuration\ /v CDNBaseUrl
if %errorlevel%==0 (goto SwitchChannel) else (goto End)
:SwitchChannel
reg add HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\Configuration /v CDNBaseUrl /t REG_SZ /d “http://officecdn.microsoft.com/pr/492350f6-3a01-4f97-b9c0-c7c6ddf67d60” /f
reg delete HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\Configuration /v UpdateUrl /f
reg delete HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\Configuration /v UpdateToVersion /f
reg delete HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\Updates /v UpdateToVersion /f
reg delete HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Office\16.0\Common\OfficeUpdate\ /f
“%CommonProgramFiles%\microsoft shared\ClickToRun\OfficeC2RClient.exe” /update user
:End
Endlocal
That’s very cool. Thanks for sharing
Hi alex, so this script will put my office version on the current channel, without central IT involvement?
Hi Konrad, Yup.
(Just recently, Central IT pushed the Deferred updates to my systems via MS System Center….and, while Windows attempted to install it, Office was stubborn enough to ignore it and keep me on Current)
I am a trainer in various financial subjects that includes using power query and power pivot.
I think PQ and PP are fantastic concepts but every time i run a course in which i demonstrate and use them at least one and sometimes half of the people in the room are unable to complete the tasks whether simple or complex because of problems with PQ and/or PP.
My worst nightmare came when PP failed to work during a presentation so i rebooted my system. PP still failed to work and it failed to work on the systems of one third of my delegates so i abandoned that part of my presentation.
These problems are expensive for a one man band like me!
And me too!
By the way, since you first alerted us to this, i have started my training sessions with the warning that for some of us Power Pivot might not work …
This is a bit of subject because it’s related to power query… But has anyone solved the out of memory issue when loading large data sets? (64 bit OS & Excel, and 16GB memory). See lots of prople online with the issue but no workable solutions.
I’m not aware of the issue.
Hi Eric
I found that stopping the data from loading in preview in the background helped with this. In Excel 2016 you can turn this off by going to Data > New Query > Query Options, selecting Data Load in the Current Workbook and then unticking the Background Data option. I also clear the data cache which seems to help too (in the same place, but under Global/Data Load).
What really worked for me was our helpful IT department giving me a dedicated server (I think they said it was a virtual server) with huge amounts of RAM to work on. I’m not technical, but our IT guys reckoned that disk space was unimportant but RAM was massively important for big queries in Power Query as all the heavy lifting is done “in memory”. I don’t really understand what that means, but hopefully it makes sense.
Eric,
Just wondering, how much is “large data sets”? You say 64-bit OS, but do you have 64-bit Excel as well? I am running 64-bit OS and 2016 64-bit Excel w/ 16GB memory and have no issues with memory as of yet. (I have some data models with over 4M records and about 8 columns of data.)
I don’t class 4M rows and 8 columns as big. I have Excel workbooks with 80M rows which are a out 380MB.
Not a bug experience but, I’m not happy with Microsoft and the availability of Power Pivot through the various versions of O365. if, as touted, this is the future of business intelligence, why isn’t it available in all versions of O365.