Don’t Update Your Power Query After Building a Model

Power Query can be a very powerful data acquisition and transformation tool, and Power Pivot is great for modifying imported models. While it isn’t always crystal clear where to perform certain operations (i.e. both tools can change the type of columns, concatenate, etc.), what is clear is the order of operations. Once you start building your model, there’s really no going back.

Consider the following scenario. You use Power Query to import some data directly into a new data model. You then massage the model, adding a new calculated column that appends a couple of imported columns.  You then realize that one of the columns has some extra trailing spaces, go back to your Power Query to use the TRIM function. Once done, you apply and close to update your model, only to be faced with an error like one of the following:

image

We couldn’t refresh the table xxxx from connection xxxxxxx. Here’s the error message we got: Out of line object ‘DataSource’, referring to ID(s) xxxxxx, has been specified but has not been used……

Or

image

We couldn’t refresh the table xxxx from connection xxxxxxx. Here’s the error message we got: OLE DB or ODBC error: The query xxxxxx or one of its inputs was modified in Power Query after this connection was added…..

I’ve seen these two, but there may be others. The upshot of it is that Power Query can’t update an existing model once structural changes have been made to it. You can overcome this error by turning off “Load to Data Model”, applying your changes, re-editing the query and then turning “Load to Data Model” back on.

image

This severs the connection with the data model, and deletes the data model (or that portion that came from the query), allowing you to create it from scratch, which is great, unless you’ve put any work into changing the model.

Data refreshes are just fine, it’s structural changes that cause a problem. The thing to remember here is that editing a model created by Power Query a one way street. There’s no going back, so put as much effort into the initial query as possible.

Hopefully this saves a few poor souls from losing a fair bit of work, but I imagine that you’ll only be reading this if you’ve experienced one of the errors above. In that case,  maybe you won’t repeat the mistake as many times as I did….

5 thoughts on “Don’t Update Your Power Query After Building a Model

  1. Pingback: Excel Roundup 20140331 | Contextures Blog

  2. Vitaly

    John, great thanks for your posts. They are very helpful. Unfortunately, your post late. I created model with lot of measures and dimensions. Later, I had to change the query and I saw error message. I unchecked “Load to Data Model”, saved query and checked “Load to Data Model”. I thought – “Wow, it working”. But when I opened PowerPivot model…. Facepalm

  3. Pingback: Don’t Update Your Power Query After Building a Model | MS Excel | Power Pivot | DAX

  4. BIfreak

    The proposed fix works fine but it does reset the calculations in PowerPivot. It is key to really think before changing any of the Queries feeding your data model. I wonder if there is a best practice to bypass this issue.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.