level: Intermediate
I have written a couple of posts here and here about the problems and dangers of using Power Query with Power Pivot. Well I am very pleased to say that I now fully understand the problem, how it is caused and how it can be avoided.
Edit: Nov ’15. Microsoft has released a fix to prevent this problem occurring.
First a Clear Statement of the Problem
You use Power Query to create a table and load it into Power Pivot. You only load a subset of columns to start with.
All is fine for a while and you create a whole lot of DAX formulae – life is sweet. You add new rows to your Power Query Table and refresh the data into Power Pivot – not a care in the world.
Then one day you need to make a change in Power Query (eg to bring in another column of data that you left out originally). You go into Power Query and make a change to your table to bring in a new column.
You save, close and load and then BAM – you get the following dreaded error message. You didn’t even see it coming before it hit you right between the eyes.
Exception occurred. (Exception from HRESULT: 0x80020009 (DISP_E_EXCEPTION))
The only option now is to remove the table from Power Pivot and then reload it. When you do this you lose all your relationships, all your measures in the table, all your calculated columns you created in the table (not that you should have any of those – right?!)
What causes this?
It is very easy to prevent this happening if you know what causes it. This is one of the many high value things I learnt at the PASS BA Conference in San Jose in April 2015 (in 2 separate sessions – one by Chris Webb and one by Miguel Llopis) . So what causes the problem? The issue is caused when you make structural changes to the table in BOTH Power Query and Power Pivot.
Let’s work through the problem.
The Connection
When you first load data from Power Query into Power Pivot, a ‘connection’ between to the 2 applications is created. You can see this connection from inside Excel on the Data Tab. See example below. Note how the connection string can be selected with the mouse.
Then you innocently make a change in Power Pivot without realising it is a problem
This is where the real problem is caused. If you make ANY CHANGES inside of Power Pivot to the structure of the original Power Query table, then Power Pivot will change the connection from being “editable” to being “read only”. If you then try to make any structural changes to Power Query AFTER the connection is set to read only, this error is then triggered. Once the connection is read only, the only thing you can do is “refresh” the model to add new rows of data. No further structural changes can be made to the table in Power Query after the connection is set to ‘read only’.
All of the following changes in Power Pivot will trigger the problem (careful, there may be more).
- Changing the table name in Power Pivot
- Changing an imported Column Name in Power Pivot
- Deleting a column in Power Pivot
Note that it is quite OK to create a calculated column inside of Power Pivot (although I wouldn’t recommended it – that is what Power Query is for).
And a big part of the problem is that you make the change inside of Power Pivot and you don’t even realise there is a problem – there is nothing to warn you or tell you that the connection has been changed to read only. It could be weeks or months later that you make the change to Power Query and trigger the dreaded message. By this time you have completely forgotten about the change you innocently made in Power Pivot.
So how do you know if you have a problem?
So by the time you get to this point, you probably realise that you may already have a problem that you don’t know about. To check, you need to take a look at the connection string between Power Query and Power Pivot. The easiest way to do this is is to goto ExcelDataConnectionsPropertiesDefinition and check the connection string (just like the example in the previous image). If you see that the connection string is ‘greyed out’ – ie not selectable and also there is a message stating properties can’t be changed, then this confirms you have a problem and it is already too late.
Time to move on with confidence
I have to say that since I have learnt the problem, I have re-engaged with Power Query like never before. I now use Power Query with confidence as a data provider to Power Pivot and I have not had 1 single instance of a problem since. The simple solution is to never ever make any changes inside Power Pivot to a table you have imported from Power Query. Take this precaution and you will never have a problem again. You can make all the changes you need directly in Power Query as long as you only use Power Query and never make structural table changes in Power Pivot. There is nothing you can’t do in Power Query anyway, so this is not a limitation in any way.
I hope this post will give you the confidence to do the same.
If you want a comprehensive lesson on how to use Power Query, checkout my training course here https://exceleratorbi.com.au/power-query-online-training/
Yes, after following the instructions I was able to download the data from Power BI dataset.
Thanks.
I was facing a similar issue in Excel while loading a table in my model using Power Pivot.
The issue arises, when our data header contains an excel reserved word. In my case, there was a column name ‘catalog_ name’ which is a reserved word for excel. I changed the column name to ‘catalog_name_1’ and the error was gone.
Bonjour Matt,
Je vous dis infiniment merci pour votre post qui vient sauver ma détresse face à ce genre de dysfonctionnement que j’ai rencontré avec ces outils tout de même puissants.
J’espère seulement que les autres dans cette même difficulté sois aussi chanceux comme moi pour retrouver votre post à ce sujet.
C’est généreux de votre part
Well! E_ACCESSDENIED which code error is 0x80020009. While working we find 12 different kinds of HRESULT Errors which code and nature of the error is different. You can see the list at https://procpedia.com/hresult-values and this page also contain the possible solution of each hresult-values. Hope it will also help you to get the solution of your problem.
Hello I have a similar problem and I think this is what happens to me as well. I have a powerpivot that pulls data from a text file. I update the text file very week for new data. Now when i refresh it loads and keeps saying its retrieving rows until it reaches row 950,000 and it says after “error.
It says: “We couldn’t refresh the connection. Please go to existing connections and verify they connect to the file or server.”.
I tried to delete the connection and start all over. And it worked , I was able to import the data all over but that means losing all my calculated columns, calculated measures and need to update all the tables in the pivot tables. This is hours of work.
I have the connection greyed out and also have the message at the bottom.
Is there a way I can edit the current connection or at least replace it with the new one so i don’t lose the calculated measures,columns, and won’t have to fix all the pivot tables i have to pull the right table?
Any help is appreciated. I am working with a huge file that will take hours to update every pivot . FYI i tried to pull this text file in a separate excel file. And it worked. But in this powerpivot file it doesn’t let me.
I am not sure this is the same issue. Can you please post your question at http://powerpivotforum.com.au as is is a better place to seek help
I tried what you said, but it didn’t work for me * sob *
I just got the same error Load to Data Model Failed
Are you using 2010?
Hello all,
I thought I post a real solution, I did not see it in the comments above. I have had this happen to me a handful of times and had to rebuild my reports due to this BIG issue with Power Query. I had the same issue again today, due to me using PQ to remove columns I “thought’ I didn’t need on my report, to realize I needed one of them.
I tried everything I have learned so far and nothing worked until I remembered this one trick that worked a while back and solved this issue.
What you do is after you modify your PQ (Power Query Editor), say you delete one of the Applied Steps, you get this message saying that you will in fact break your connection to the Data Model; that is OK. Do it and save and load.
On the Power Query Tab, in Excel, click “Show Pane” under Workbook Queries. You will see a yellow triangle with a message that it could not load data, or something to that effect. Don’t panic. I did. Right click on that connection and select “Load to…”; once there find the “Add this data to the Data Model” and UNTICK it and click “Load”. Wait for it to change to Connection Only. Right click on that connection and select “Load to…”; once there find the “Add this data to the Data Model” and TICK it and click “Load”. Once you do this it should start reloading the data to the Data Model and you should be good to go. The only issue with this is that your PowerPivots lose the “Values” within them so you will have to remember how your selections were done.
I hope this helps you and know that I have done this a few times with 100% success.
Good luck!
Christian D.
Hi Matt, thanks for the really clear and helpful blog post.
I’m using Excel 2013 (O365 click to run so I think up to date) where I thought this issue was fixed i.e. that you can’t modify the column names in PowerPivot, you have to do it through the PowerQuery window (the UI prevents it – as per Ken Puls’ post http://www.excelguru.ca/blog/2015/10/21/breaking-power-query-via-power-pivot-is-a-thing-of-the-past/#comment-261235).
I’ve recently renamed a couple of columns in PowerQuery, and seem to have triggered the dreaded HRESULT 0x800A03EC error (although I can’t be absolutely sure this is what caused it).
None of the 7 connection strings are greyed out. If I try to right-click refresh on the Workbook Queries pane, it says then says “Download Failed” (little yellow warning symbol) and allows me to choose Load to “Connection Only” / “Data Model” once again. This seems to create duplicate copies of all my tables in PowerPivot For Excel window.
Have you any experience of this in Excel 2013 since the don’t-edit-me-in-powerpivot-edit-me-in-powerquery issue was fixed? I’m not sure how to avoid the issue especially if I haven’t ended up with the greyed out connection string and didn’t edit anything in PowerPivot… many thanks
I am also using the o365 version and definitely have the warning message “cannot edit, go to Power Query to Edit”. I have not had any issues since this change (ie zero). Now of course that is a sample of 1, however I have also never heard from anyone that they still have issues – until now.
Sorry, I don’t have any other insights
Thanks Matt.
It’s good to know it’s a likely an exception rather than the norm. I spent a while trawling through all the reports of previous misery thinking oh no I can’t use this buggy mess, until I realised that it had been (generally) fixed. It’s great that everything is moving so fast, but it means you have to have your wits about you when you are reading blog posts!
The whole model crashed out and hung the PC about two weeks ago so I think there may be a glitch in the matrix. The fact the connection strings were still editable certainly makes it seem like a slightly different issue.
I have an earlier back up so I am testing adding new columns and renaming those, rather than renaming the existing columns. Hopefully that’ll limp me through until I can commit the whole darn thing to PBI desktop instead. Oh how I wish PIBD had pivot tables too… spoiled for choice I suppose.
Take care.
Hi Matt,
Hallelujah Hallelujah Hallelujah
Yesterday I was the most happiest man in the world …… simply because of you 🙂
It is almost three months trying to build my tables to compute the cost of manufactured goods for various products, and this requires to furnish many individual tables and link them together to reach the cost for each product (this includes imported materials, custom duties, transportation, banks, salaries, sales,…. etc.) and finally to reach my Income Statement.
I prepared 28 Excel sheets and tables, pulled them to PQ, and then to PV. during these period I need to restructure my PQ files and to refresh it in my PV tables but I started to face the very silly night mere message (Exception from HRESULT: 0x80020009 (DISP_E_EXCEPTION)).
Yesterday while Googling the error, I have read your fantastic solution and I started to carry it out your idea and started to create not only one table but various parallel tables to the original tables and according to my DAX formula. I took my time to extract formulas from the original tables and paste it to the new helper tables and it worked perfectly.
Thanks Matt for your great great help …. Bless you.
Lotfy Kozman
? glad it worked for you
Thanks a lot for these informations!
It seems like this bug has been fixed with Excel 2016. If anyone can confirm, we can use the new power query without the workaround.
Hey Shawn,
Indeed, Excel 2016 was designed to remove this bug, and prevent you from doing things that would accidentally break the ability to refresh Power Queries. Specific actions that are prohibited are renaming columns or tables, and deleting columns sourced from Power Query.
The even better news is that this is also now fixed in 2013 as well. I actually just published a post on this a few minutes ago, which you can pick up here: http://www.excelguru.ca/blog/2015/10/21/breaking-power-query-via-power-pivot-is-a-thing-of-the-past/
Hey Ken. I noticed that I get the “can’t edit this table because it was created in Power Query” message now, but I hadn’t made the connection that MS had actually “fixed it”. Thanks for sharing
Ken / Matt,
Do you know if there’s a backwards compatibility issue between Excel 2016 and 2013. I used Excel 2016 Get & Transform to create a query hat feeds into Power Pivot. When I try to run this in Excel 2013 however the data load fails with this warning message
Cannot connect to the data source for table “CGRawData”. Make sure that Data Source is available. If you contact Microsoft support about this error, provide the following message:
Failed to connect to the server. Reason: The connection could not be refreshed. This problem could occur if the connection was copied from another workbook or the workbook was created in a newer version of Excel.
CGRawData is my Table/Query name
Up until now I was not aware of any issues. But it certainly sounds like a backwards compatibility issue. You could try to cut and paste the power query code into a clean excel 2013 workbook and see where it fails. Maybe there is a compatible transformation you can use instead of the failed item. Just a thought.
It’s interesting as the Microsoft Power Query team got back to me today and suggested installing the fixes that were used to address this original issue.
“Make sure you update and install KB3039739, KB3085502 and KB3039800 to see the fix working”
However I tried out my 2016 file on my old 2013 machine that had these fixes installed and I still got the same problem.
Unloading the query from Power Pivot while in Excel 2013 and then re-loading it worked fine, but then we’re back to the old issue of losing reports, measures etc…
Clearly this is a pain as most of our clients are on Excel 2013
I’ve replied to them with as much detail as possible and will keep you posted on what I hear back.
Solved.
There is a patch for this, however a super simple workaround is to just save the file while in Excel 2013 before refreshing the query
https://support.microsoft.com/en-us/kb/3101561
Matt,
I think there is another warning that is appropriate:
If one creates an Excel table and uses it as a source with Power Query, do not make changes to column names in Excel afterwards; make your changes in Power Query instead.
May apply to any table used as a source to Power Query.
That’s interesting. I have never had a problem here. I have just tried to add a new column in Excel for a table already connected to Power Pivot via Power Query and it works fine. What issues have you had?
Thank you so much for sharing !!!!
You save my morning and mental wellness
Thank you
Hey there Matt,
I read your blog post when you posted it.
And today I was trying to refresh a CSV file which is used within Power Query. Initially I had the CSV file in a different location. So I went into Power Query, and changed the Source to look at the new location. One I did that, it all looked good within Power Query.
I then clicked on Close and Load, and it will not load the updated data into Power Pivot. I have not made any changes to my Power Pivot window, or table or anything.
I am going to assume that if I make a change to the Power Query Source, that this modified the connection and makes it read only?
I even tried creating the folder structure so that when I open my Excel File and try and click on refresh, it will find the CSV file where it thinks it should be. But that still fails.
Any idea’s?
Thanks
Gilbert
well this does sound like the same issue. You cannot change anything once it is read only, including the file location. I had the same issue. Follow my instructions above to see if the connection string is greyed out. If it is greyed out, it is read only and you cannot change anything. Assuming this is the problem, you either need to put the file back in the original location, or remove the table from Power Pivot and then reimport it. You will lose any connections you have and any measures inside the table. You can
1.manually move the measures to a new table,
2. remove the table
3. reimport
4. move the measures back into the table
5. reconnect the tables.
It is a pain, but given you now know what the issue is, this should be the last time you have to do this 🙂
I’ve just been trying to set things up in Excel 2010 for a client (Power Query manipulating the data and then pulling it into PowerPivot via existing connections).
I keep hitting the issue of the connection breaking as soon as I save the file. Is there any way around this other than copying and pasting the existing connection string each time?
The following should work.
1. Create the Power Query, click Close and Load To, and select “create connection only”.
2. Go into the Power Pivot Window, go to the Design TabExisting Connections and select the Power Query connection you created.
Everything should work from there as a normal connection without issues.
Is this how you are doing it?
Hey Matt –
Thanks for the insightful post! I’m running into am issue that honestly I am completely lost wIth. I feel like I’ve tried everything short of recreating my workbook from scratch. I’m using this as the last resort because my model includes some 30 queries or so.
I’ll try and breakdown the issue as best I can. Essentially, I created a series of functions that wrap two parameters into some teradata sql in the where statement. I then use an excel parameter table with two columns as the two parameter inputs, call the function to have my modified query. This all works fine and loads into power pivot successfully. The problem comes in when I save excel. The moment I save excel all of the power query strings change for no reason I can see. I have made no modifications to the power queries so I have no idea why they are changing. The error I’m getting indicates that it may be related to creating the query in a newer version of excel.
One thing to note is I created all the queries except the queries with the embedded functions in power query 2.18 and the embedded function queries in power query 2.22. Would the queries created in the earlier version be causing this issue? Is my only answer to recreate the model in a fresh workbook? Any help would be greatly appreciated!
Thanks
Kellan
Hi Kellan
This is a pretty technical scenario so it is hard to know where the failure is – but then again I guess you already know that right?!
I have seen no evidence that a newer version of Power Query has any problems with queries created in older versions. Of course that doesn’t mean it can’t happen. Given the number of updates that have come out, I think MS would be all over this.
Have you tried contacting the MS team with the frown button in Power Query? I have found them to be very responsive.
Hey Matt –
Thanks for the response! You’re right, it was definitely a technical scenario lol!
So I wanted to wait until the MS team released their newest update to see if it was just a minor bug. And it was! It turns out that there are scenarios where every time you save the workbook all of the Power Query connection strings change. The newest release yesterday fixed this issue and I’m off and running!
Excited to be back to building 🙂
Cheers,
Kellan
Good timing. Had this problem this morning.
To not change Power PivotOrQuery after a Power Query2Pivot kind of takes the beef away.
I hope Microsoft can solve the problem before it becomes the published norm (in books).
Ola, just to clarify. It is 100% ok to change Power Query all you want. Just make sure you always make any changes you need in Power Query and never in Power Pivot. That way you will get everything you need and won’t have any issues.
What about creating a column to get data from other table using the Related()?
Calculated columns are fine, just don’t change any existing columns.
Hello, I appreciate your guidance! However, I received the error “Load To Model Failed” and all I did was REFRESH the table within power query- I made no structural changes to the table, but the table needed refreshing because it went from 42 rows to 50 rows of data… would this matter?
Well if it were me I would open the power query, go to the first step, refresh the preview, then work out where the issue is from there
Hi,
Can somebody help me with this error
SELECT [‘2016$’].* FROM [‘2016$’]
UNION ALL
SELECT * FROM ‘C:\Users\saysfv\Documents\2017\2017-test.xlsx’.[‘2017$’]
The SQL statement is not valid. There are no columns detected in the statement.