Power Query Refresh fails after making certain changes in PowerPivot Model

As you can imagine, I am really enjoying playing with Power Query and Power Pivot.  PowerPivot models have been around a bit longer so the software is pretty stable.  Power Query is still pretty new, so occasionally we run into some “gotchas” with the software, this post is intended to help you avoid a few of these “gotchas”.

Members of my team were happily creating PowerPivot models with the data sourced in Power Query, and out of the blue, mysterious errors kept occurring:

• Exception from HRESULT: 0x800A03EC
• Could not refresh table ‘Survey’ from connection ‘Power Query – Survey’.  Here’s the message we got:
  Out of line object ‘<xxx>’ referring to ID(s) ‘<xxxGUID>’ has been specified but has not been used.  The ‘<xxx>’ column does not exist in the rowset.

Upon further research and happily landing on this thread (and the brilliant Miguel Llopis) led us understand that there are some common “gotchas” which cause your Power Query to be marked Read Only (bad), and thus not refreshable.  Once you are in this state, the only way out is to remove the query from the Data Model and put it back in (see steps below for renaming a PowerPivot table).

To help keep you out of trouble, in a PowerPivot model where the data is sourced from Power Query do NOT perform any of these operations DIRECTLY in the PowerPivot model:

(If you are using the native Power Pivot data connections for ALL tables in the model, these do not apply)

• Don’t Edit Table Properties – it seems like you could remove a column from the model by de-selecting it from Table Properties, but don’t do it!
  • Go back into the Power Query source query for the table and use a Remove Column function to remove the column, then reload to Power Pivot.

• Do not Delete, Rename or change the Data Type of a Column in a Power Pivot table.
  • Go back into the Power Query source query for the table and use a Remove Column, Rename Column, or Change Data Type function to make the changes, then reload to Power Pivot. 

• Don’t Delete a Table directly in the PowerPivot window
  • Go back into the Power Query source query for the table uncheck the box for “Load to Data Model”. If you REALLY don’t need the query anymore, you can delete the query and it will also remove it from the PowerPivot data model. 

• Don’t Rename a PowerPivot table (and underlying Power Query)
  • For this one, you will have to break your PowerPivot data model. In Power Query,
    Go back into the Power Query source query for the table uncheck the box for “Load to Data Model” Apply and Close to refresh.
    Edit the query and Change the name of the query, Apply and Close to refresh (but don’t re-check the box just yet).
    Edit the query and re-check the box for “Load to Data Model” Apply and Close to refresh – this will also load the table back to the Data Model with the new name.
    Note: You will have to re-apply your relationships and also add back any calculated columns which had previously been on the PowerPivot table. This is why it is a good idea to think through your naming conventions early.

• Import additional PowerPivot tables using PowerPivot Import Wizard. Even if it is a really simple query, use Power Query.
  • So if one PowerPivot table is Power Query, they all must be Power Query

• Don’t try to upgrade an existing Excel 2010 Data Model built using Power Query to an Excel 2013 Data Model.
  • This one actually makes a ton of sense to me. As Excel 2010 cannot load data DIRECTLY to the Data Model, you have to use Load to Worksheet then create a linked table to get the data into the Data Model. Recreating this model directly in Excel 2013 would result in a much more streamlined model.

One way to tell if you are creating trouble for yourself is to check your Power Query data connections to see if they are still OK.

On the Data Ribbon in Excel, select Data Connections

Open each Power Query data connection, and view Properties

On the Definitions tab of the Properties window, look at the connection string.  If the Connection String box is white and looks like you could edit it, then you are OK.  However if the Connection String box is greyed-out, then “Houston, you have a Problem”!

At this point, you will need to disconnect the Power Query from the Data Model.

Right click the Power Query and select Edit.

De-select both the Load to Worksheet and Load to Data Model check boxes.

Apply and Close the Power Query.

Right click the Power Query again and select Edit.

Re-select Load to Data Model check box (and Load to Worksheet if needed).

You will need to put back any updates to the table in the PowerPivot model, such as relationships, calculated columns, and measures.

Power Query Version at the time of posting: (2.12.3660.142)

(Due to new bits for Power Query being published essentially monthly, I will note the Power Query version # when the post was created)