I’m pleased to let people know that breaking Power Query via Power Pivot is a thing of the past … at least for users of Excel 2013 or higher. (Sorry, if you’re on 2010, you still need to be careful.)
The information has been around for a bit, and it’s one of the topics we cover in our http://powerquery.training/course as well: how to break your Power Query by doing one of the following actions in Power Pivot:
- Renaming a table
- Renaming a column sourced from Power Query
- Deleting a column sourced from Power Query
Any of these three actions would set your query into an un-editable state, but worse, nothing would appear to happen. The query would refresh as normal, until you eventually tried to change it. At that point all hell would break loose and your only option was to rebuild your query (and related data model table) from scratch.
This has been covered in detail in the following sources:
- Matt Allington's Blog
- M is for (Data) Monkey e-Book (and yes, you can download a copy NOW!)
But now, breaking Power Query via Power Pivot is a thing of the past…
This issue was fixed in Excel 2016, but it left many of us hanging with an older version that still exhibited the problems. If you’re on 2013, however, that problem has now been fixed. I share the links at the bottom of the post to make sure you’re updated, but first I’ll demonstrate that the fix is really working.
To set the stage, I created a simple Calendar table in Power Pivot, and loaded it to the Data Model.
Corruption Method #1: Deleting Columns
My first test was to attempt to delete the Year column in Power Pivot. At first it looks like nothing has really changed:
But when I click Yes, Power Pivot comes back with a message to let me know that I can’t do it after all:
Hooray! This is fantastic news, as it means that I can’t actually destroy my entire data model. Beautiful!
Corruption Method #2: Renaming Columns
Next I tried to rename the Year column to myYear.
Nope. Can’t break the model that way either.
Corruption Method #3: Renaming the Table
Finally, I tried to rename the table from Calendar to myCalendar:
And it looks like we’re protected from shooting our model in the foot too.
My thoughts on the fix
I’m 99% happy with this fix. It protects us from accidentally blowing up our data models, which is super important. Especially because it was possible to break the model and still run for months without every realizing it. That just shouldn’t be allowed to happen. So why am I not 100% happy?
Well, the first part is that Excel 2010 users are still susceptible to the issue. That’s a challenge, although to be fair Microsoft has been pretty forthcoming that the Load to Data Model hack is not truly a supported method anyway. So really, there’s not much of a surprise there. I’m not holding any points back on this one.
The last part – the remaining 1% for me - is that the fix, as implemented, means that you cannot ever rename a table in Power Pivot that was source from Power Query. In fact, even if you go back to Power Query and rename the table there, it still shows under the original name in Power Pivot. Granted it’s not a total show stopper, but you do want to give some thought to your query naming before you push it into the data model that very first time.
How can you ensure you have the fix?
If you’re running automatic updates for Office 2013, you should already have the fix in place. But if you want to check (or you don’t), then here’s the deal:
The full support KB article on the subject can be found here.
It will direct you to install the following updates:
- KB3039800: update for Office 2013 – From October 13, 2015
- KB3039739: update for Office 2013 – September 8, 2015
- KB3085502: MS15-099 security update for Excel 2013 – September 8, 2015
(There is a 32 and 64 bit version of each, so make sure you pick up the right version.)
For reference, I just tried to install them, without checking if they’d been installed first. Fortunately it does a check first, so for me each of them came back with a message like this:
So there, you go. Great news for users of Power Query and Power Pivot 2013 and higher. You can now model with the confidence that you won’t accidentally blow up your solution!
7 thoughts on “Breaking Power Query via Power Pivot is a thing of the past”
Hurrah !!!!!
Hurrah !!!!!
This is a VERY important news for any Power Pivot user.
Please promote as much as possible this news diffusion.
Fantastic, i was just demonstrating this horrible issue last week.
It is very good news!!!
Thanks for sharing, Ken
Hi Ken, thanks for sharing. It gives me a better understanding of why the option to change table in PowerPivot is greyed out.
I have just managed to break a model in Excel 2013 anyway it seems (click to run O365 version so should be up to date). I recently renamed a couple of columns in Power Query in a workbook with plenty of queries, measures and relationships. There are 7 Query connections listed under "Connections" and one ThisWorkBookDataModel. I've checked all of them and none of the connection strings are greyed out.
However, I'm still getting the HRESULT 0x800A03EC and "The Data Model could not be refreshed" error
Returning to the excel worksheet and using the Workbook Queries pane, I've 'Refreshed' each query which gives a 'Download Failed' icon which then allows me to reselect Load To... Data model > Connection Only once again. However, when I go to the PowerPivot for Excel window, it has duplicated all of my tables with a "1" appended to the name. The new ones will refresh in PowerPivot for Excel window, the original ones won't.
Looks like I'm going to have
a) "1" after all my carefully considered table names
b) To move all my measures to the new versions of the tables
c) To redo all my visuals and PivotTables to reference the new tables.
I'm sharing this here just in case others are still experiencing the same issues in Excel 2013.
Any advice greatly appreciated, if any of the above makes sense.
Yikes, that sucks. Not sure what to suggest here, and honestly, I'd be lacking some trust in my model at this point, and would probably rebuild it. Having said that, I don't know how big your model is, and if that is a viable thing here.
Strange thing the document wouldn't save either.
It's my first big one, I've kept previous versions pretty regularly so I am going back to the last working one and testing changes in PowerQuery pretty carefully to see if I can replicate what breaks it. I don't mind rebuilding a model, but not if I can't trust it won't keep happening! That would suck.
I'd love to switch to PB Desktop but don't think I can deliver client req without pivot tables. Excel 2016 might be more stable?
I've had some strange crashes and errors in this spreadsheet in the past which I seem to have gotten past by closing and reopening the spreadsheet. Probably a veritable bug feast for the MS team if I could track them down (like the sort/filter hover-over title bars disappearing from visualisations in PowerView, popups like "We could not get data from the data model, Collection was modified; enumeration may not execute")
The tests I'm running at the minute are to duplicate the column I am trying to rename in PQ, rename the copy. Then replace this into all the pivot tables and measures and Powerview sheets. Seems to be refreshing ok when I do this. Next I'll try deleting the 'old' column once I'm pretty sure it's not being used anywhere.
Thanks again
For sure, backups are still a pretty important strategy when building Power Pivot/Power Query solutions. I've seen (since this update) a model that just disappeared with no warning. (Just wasn't there when the workbook was re-opened.) So developing with these tools certainly isn't without it's pain, unfortunately. At least you had a backup, that's a good thing!