Anyone who has tried to do any serious work with Power Pivot and Power Query will know about this problem: you use Power Query to load some tables into the Data Model in Excel 2013; you make some changes in the Power Pivot window; you then go back to Power Query, make some changes there and you get the dreaded error
We couldn’t refresh the table ‘xyz’ from the connection ‘Power Query – xyz’. Here’s the error message we got:
COM Error: Microsoft.Mashup.OleDbProvider; The query ‘xyz’ or one of its inputs was modified in Power Query after this connection was added. Please disable and re-enable loading to the Data Model for this query..
This post has a solution for the same problem in Excel 2010, but it doesn’t work for Excel 2013 unfortunately. There is a lot of helpful information out there on the web about this issue if you look around, though, and that’s why I thought it would be useful to bring it all together into one blog post and also pass on some hints and tips about how to recover from this error if you get it. This is the single biggest source of frustration among the Power Query users I speak to; a fix for it is being worked on, and I hope it gets released soon.
Problem Description
Why does this problem occur? Let’s take a simple repro.
- Import the data from a table in SQL Server using Power Query. Load it into the Excel Data Model.
- Open the PowerPivot window in Excel, then create measures/calculated fields, calculated columns, relationships with other tables as usual.
- Go back to the worksheet and build a PivotTable from data in this table, using whatever measures or calculated columns you have created.
- Go back to the PowerPivot window and rename one of the columns there. The column name change will be reflected in the PivotTable and everything will continue to work.
- Re-open the Power Query query editor, and then rename any of the columns in the table (not necessarily the one you changed in the previous step). Close the query editor window and when the query refreshes, bang! you see the error above. The table in the Excel Data Model is unaffected, however, and your PivotTable continues to work – it’s just that now you can’t refresh the data any more…
- Do what the error message suggests and change the Load To option on the Power Query query, unchecking the option to load to the Data Model. When you do this, on the very latest build of Power Query, you’ll see a “Possible Data Loss” warning dialog telling you that you’ll lose any customisations you made. Click Continue, and the query will be disabled. The destination table will be deleted from your Excel Data Model and your PivotTable, while it will still show data, will be frozen.
- Change the Load To option on the query to load the data into the Excel Data Model again. When you do this, and refresh the data, the table will be recreated in the Excel Data Model. However, your measures, calculated columns and relationships will all be gone. What’s more, although your PivotTable will now work again, any measures or calculated columns you were using in it will also have gone.
- Swear loudly at your computer and add all the measures, calculated columns and relationships to your Data Model all over again.
So what exactly happened here? The important step is step 4. As Miguel Llopis of the Power Query team explains here and here, when you make certain changes to a table in the Power Pivot window the connection from your Power Query query to the Excel Data Model goes into ‘read-only’ mode. This then stops Power Query from making any subsequent changes to the structure of the table.
What changes put the connection to the Excel Data Model in ‘read-only’ mode?
Here’s a list of changes (taken from Miguel’s posts that I linked to above) that you can make in the PowerPivot window that put the connection from your query to the Data Model into ‘read-only’ mode:
- Edit Table Properties
- Column-level changes: Rename, Data type change, Delete
- Table-level changes: Rename, Delete
- Import more tables using Power Pivot Import Wizard
- Upgrade existing workbook
How can you tell whether my connection is in ‘read-only’ mode?
To find out whether your connection is in ‘read-only’ mode, go to the Data tab in Excel and click on the Connections button. Then, in the Workbook Connections dialog you’ll see the connection from Power Query to the Data Model listed – it will be called something like ‘Power Query – Query1’ and the description will be ‘Connection to the Query1 query in the Data Model’. Select this connection and click on the Properties button. When the Connection Properties dialog opens, go to the Definition tab. If the connection is in read-only mode the properties will be greyed out, and you’ll see the message ‘Some properties cannot be changed because this connection was modified using the PowerPivot Add-In’. If you do see this message, you’re already in trouble!
How to avoid this problem
Avoiding this problem is pretty straightforward: if you’re using Power Query to load data into the Excel Data Model, don’t make any of the changes listed above in the PowerPivot window! Make them in Power Query instead.
How to recover from this problem
But what if your connection is already in ‘read-only’ mode? There is no magic solution, unfortunately, you are going to have to rebuild your model. However there are two things you can do to reduce the amount of pain you have to go through to recreate your model.
First, you can use the DISCOVER_CALC_DEPENDENCY DMV to list out all of your measure and calculated column definitions to a table in Excel. Here’s some more information about the DMV:
http://blog.crossjoin.co.uk/2011/09/17/documenting-dependencies-between-dax-calculations/
To use this, all you need to do is to create a DAX query table in the way Kasper shows at the end of this post, and use the query:
select * from $system.discover_calc_dependency
Secondly, before you disable and re-enable your Power Query query (as in step 6 above), install the OLAP PivotTable Extensions add-in (if you don’t already have it) and use its option to disable auto-refresh on all of your PivotTables, as described here:
http://olappivottableextend.codeplex.com/wikipage?title=Disable%20Auto%20Refresh&referringTitle=Home
http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=26
Doing this prevents the PivotTables from auto-refreshing when the table is deleted from the Data Model when you disable the Power Query query. This means that they remember all of their references to your measures and calculated columns, so when you have recreated them in your Data Model (assuming that all of the names are still the same) and you re-enable auto-refresh the PivotTables will not have changed at all and will continue to work as before.
[After writing this post, I realised that Barbara Raney covered pretty much the same material in this post: http://www.girlswithpowertools.com/2014/06/power-query-refresh-fails/ . I probably read that post when it was published and then forgot about it. I usually don’t blog about things that other people have already blogged about, but since I’d already done the hard work and the tip on using OLAP PivotTable Extensions is new, I thought I’d post anyway. Apologies…]
UPDATE: this problem is now fixed with the following Excel updates: https://blogs.office.com/2015/11/10/power-pivot-in-excel-2013-november-2015-customer-update/
Reblogged this on MS Excel | Power Pivot | DAX and commented:
Excellent job, thank you for sharing
I have to admit that the PowerBI stuff gives me the creeps – imagine to have to deliver the numbers but the tool fails with an error message like above, with no direct way for a fix – that’s scary. To me it still has some kind of beta-testing aura. Even more, the error does not seem to be unfamiliar, in case you really try to build something other than very basic stuff with the tools.
But I have to admit, I didn’t do much with it, I am clearly not expert. So, am I wrong here? Is it a false impression I got? What do you think?
In this case there’s an easy way of avoiding this problem, although it’s highly likely people will make this mistake, find out what’s happened and have to go through the pain of fixing it at least once. However I can completely understand why you have the impression that you have – this kind of problem shouldn’t exist at all. The Power BI stack has some great functionality but you’re right, it does have a slightly unfinished feel in places and doesn’t hang together as well as it should. My guess is that it’s the decision to use Excel as a platform that is the problem, and the Excel team aren’t being as responsive as they should be when it comes to dealing with issues like this. Excel is everything to the Power BI stack, but the Power BI stack is only one priority among many for Excel. This could also be why we are seeing a move away from Excel and into the browser for the latest Power BI functionality, including report generation.
Thanks, Chris! I see that bringing it to the browser gives more flexibility by providing access to the latest technology. But having nice features is not enough anymore in today’s world, it also needs to be accepted by the users: from all I experienced the tightly integrated Excel integration is the killer feature for any BI-tool from MS. The self-service path clearly points towards the controllers as the main users, and they want Excel. Let’s hope that MS is choosing the right path onward, maybe towards some kind of hybrid and answering the question of balancing it just right… But if MS is really risking the move away from Excel, it makes MS BI just one of many, so definitely this sounds like a strategic move with impact.
I don’t think there will be a complete break with Excel, and remember that any strategy to move to the browser is just speculation on my part. MS knows very well what an important asset it has with Excel.
Chris,
I’ve come across a situation where I’m getting the behavior described in this post, but I’m not changing anything in PowerPivot. The only change is to edit the query in Power Query to add a condition to the WHERE clause to filter out test data. The query change refreshes fine in the Preview panel of the query editor, but when the ‘Close and Reload” button is used, the errors ensue. Power query Version: 2.16.3785.242
Chris,
Upon further digging, it does appear that the original designer of the model did use PowerPivot to make breaking changes to the Power Query tables in the model. The odd thing is that everything works up until the point I try to modify the Power Query source to add the filter clause. Then I get the dreaded error…
Yes, unfortunately that’s the way it happens…
Thanks for the excellent post! After believeing that it wouldn’t matter where the changes were made and that the stack would simply manipulate the data sequentially, I now have a useless model that I need to re-work. At laset I have some best practice to share with my colleagues so we can avoid the problem in the future.
Just installed SharePoint in our company. I’ve built all these amazing reports in power BI with Excel 2013 power pivot. Go to make a change and then this error pops up. I now understand the reason why and the solution (excellent post – thank you), just wish this had been explained at the start as I would have done all of the editing in power query rather than in the power pivot tables.
Hi Chris, great post thanks.
I’ve managed to trigger some variation of this in Excel 2013 (15.0.4787.1002) where I believe this issue was fixed. I can’t edit the pivottables in the PowerPivot window anymore. I changed some column names in PQ and have managed to get into a situation where the pivot tables won’t refresh. The connection strings are not greyed out. Have you any experience of this in Excel 2013 since the issue was fixed? It seems related, but not exactly the same.
No, I haven’t encountered anything like this. Do you see an error message anywhere?
Yes, Exception from HRESULT: 0x800A03EC
If I try to right-click the busted query and refresh on the Workbook Queries pane, it says then says “Download Failed” (little yellow warning symbol). If I mouse over to the right it allows me to choose Load to “Connection Only” / “Data Model” once again. This seems to create duplicate copies of ALL of my tables in PowerPivot For Excel window with suffix .1
I’ve gone back to an earlier version of the workbook before the issue and instead of renaming the column directly (which had already been used in measures and visualisations etc), I thought I’d duplicate the column, rename the duplicate, go through all measures and visualisations/pivot charts replacing the original column with the renamed duplicate. It may be a coincidence, but I haven’t had any major issues since.
It’s a bit disconcerting having the threat of models blowing up and having to redo from scratch. Now that Read-only connection issue is fixed in Excel 2013 and in Excel 2016, do you still follow practises like putting all your measures into a separate table? Am considering moving to Excel 2016 so I can become adept at importing exporting models with VBA. Copying and pasting all the measures and queries into a document and then back into a model must be soul destroying. Haven’t had to do it yet, was hoping I had dodged that bullet with the Excel 2013 fix.
Take care,
Brian
That sounds like a different bug to me – you should use the “send a frown” button if it happens again.
Remember, you can always export all your DAX code from the model using this DMV: http://blog.crossjoin.co.uk/2011/09/17/documenting-dependencies-between-dax-calculations/
In my case i imported a certain worksheet from one file and once i put another file with similar format and tried to update power query table it didn’t work
what might be the problem than?
the file were in a folder and than latter on i put another file in that same folder too for the power query to integrate worksheets of both two files but it didn’t work after i refreshed it