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:

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

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.

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