Solved: Power Query Issues with Power Pivot – Exception from HRESULT: 0x80020009

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.

initialdata

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.

refreshdata

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.

powerquery change

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

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?!)

L2WD banner ad

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.

connection string

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).

  1. Changing the table name in Power Pivot
  2. Changing an imported Column Name in Power Pivot
  3. 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.

connection string

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.

May 16, 2015 11:58 am

27 Comments

  • 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).

    1. 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.

  • 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?

    1. 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?

      1. 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

        1. 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.

          1. 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

  • 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

    1. 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 🙂

  • 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.

    1. 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?

  • 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.

    1. 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/

      1. 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

      2. 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

        1. 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.

          1. 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.

  • 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

  • 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

    1. 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

      1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*

Bring your data to life - Let's discuss how Matt can help!