Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Reusing A Power Query Connection

with 11 comments

One of the things I like least about Power Query at the moment is the lack of control over where the results of your queries end up in Excel. For example, if you create a new query and load it onto a worksheet, your table always ends up in the top left hand corner of a worksheet. Furthermore, it seems like every time you refresh your query, then the table it’s bound to gets deleted and recreated so any extra calculated columns or formatting you have added to the table disappear.

There is, however, a way around this. If you go to the Data tab in Excel and click the Existing Connections button you can see the OLEDB connection that Power Query creates in the “Connections in this Workbook” section:


You can then double-click on the connection and create a new table from that connection, call it whatever you want, add formatting, calculated columns and so on and all of these things will remain intact when you refresh. Even better, when you refresh the table it will re-execute the underlying Power Query query. This is a much better experience for consuming the results of your Power Query query, in my opinion.

Incidentally, Miguel Llopis of the Power Query team told me on the forum that this is an area of functionality that they hope to address in early 2014, so hopefully this blog post will be redundant in a few months.

Written by Chris Webb

October 25, 2013 at 6:19 pm

Posted in Power Query

11 Responses

Subscribe to comments with RSS.

  1. Hello Chris:

    I am working on on-premise data access using Power Query on Excel. I am able to pull data using Data Management Gateway on Excel and then uploaded the Excel on office 365 site. The problem is I am not able to refresh the data on Office 365 portal. Is this a known issue? Any work around for this?



    November 4, 2013 at 10:32 am

  2. Thanks for the quick reply Chris. I am trying o Power BI site only and trying to create oData feed for a on-premise data. I read the blog and response from John as the feature is only available with anonymous oData feeds, and Project Online data feeds. When can we expect oData feed for on-premise data with Microsoft Online Service Id authentication?



    November 4, 2013 at 11:15 am

  3. Thanks for your tip. It has indeed allowed me to keep a calculated column. However, though it initially seems to work, after some time, the table loses its custom formats upon refresh. I have a date column which I set to the date format in power query. But no matter what I try, it ends up being displayed as integers. But at least my calculated column stays. (Using Excel 2010 & PQ Version: 2.9.3502.261)


    January 14, 2014 at 9:55 am

    • hello Bertrand… I am running into this exact same issue. Did you find what was wrong? Please let us know if you found the solution. I will go back to search on this and probably pull my hair off shortly…

      Sachin Mundra

      June 19, 2014 at 1:59 pm

      • Sachin, this problem should be fixed in the latest version of Power Query. Have you got that installed?

        Chris Webb

        June 19, 2014 at 5:51 pm

  4. Hi Chris,
    another question regarding PowerQuery-Connections and PowerPivot-Usage of the data:

    I have created a nice PPivot-Table based on a PQ with a .txt-file with KPIs and some measures underneath. THEN I gave the file to my collegue, who tried to change the underlying sourcefile. He ended up with a destroyed PPivot-Table and a message like OLE-DB Error, blablalba, try to deactivate and reactivate the connection. I’ve changed the PQ to load its data to an Excel-Sheet, used that sheet in the PP. Now the guy has to update two parts of the file: the PQ to load the data to the Excel-sheet and the PPivot-table to reflect the changes in the Excel-sheet.

    Is there a better way to change the name of the sourcefile and still beeing able to load data without destroying the PPivot-model?

    Best Regards


    February 25, 2014 at 6:39 pm

    • Sounds strange – did your colleague have the same versions of Excel/Power Pivot/Power Query installed? How did he try to change the source file? Did he get the error in Power Pivot or Power Query?

      Chris Webb

      February 25, 2014 at 10:45 pm

  5. Hi Chris,
    I have created a PowerQuery connection to a PostGresSql database and now I promoted the powerpivot to a tabular Cube. Now the powerquery connection is added as a PushedDatasource. I have 2 questions you hopefully can answer:
    1. Is there a way to reuse the powerquery connection?
    2. An other option would be connection via ssas – odbc to postgressql but that is not working for me. Any advice in that area?

    Hans Peeters

    November 27, 2014 at 11:08 am

    • There’s no way, at the moment, to use Power Query to move data into SSAS Tabular. It’s something that a lot of people have asked for though so I hope it comes soon. I’m afraid I have never used PostGres with SSAS so I don’t know why the direct connection doesn’t work; I guess it’s because the SQL that SSAS generates isn’t compatible. The first thing to try would be to see whether you could create a linked server in SQL Server and connect through that.

      Chris Webb

      November 27, 2014 at 11:47 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 3,962 other followers

%d bloggers like this: