Chris Webb's BI Blog

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

Reusing A Power Query Connection

with 9 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:

image

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

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

    Thanks,
    Saurabh

    Saurabh

    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?

    Thanks,
    Saurabh

    Saurabh

    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)

    Bertrand

    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
    Patrick

    Patrick

    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


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Follow

Get every new post delivered to your Inbox.

Join 3,202 other followers

%d bloggers like this: