Publish Power Query Connection for Use By Other Workbooks

[blimbert]

Hello-I have a series of power queries that culminate in a large dataset of more than 1 million records. I would like to publish access to the power query as a connection, rather than as a workbook table given it’s large size and growing.

In a nutshell, I’d like to allow access to the power query connection from workbook A, to other workbooks to summarize or trend the data as needed.

It feels as this should be a simple task but I am really struggling with how to accomplish.

Thank you!

 

[jdellasala]

There is a way to publish a query to others, however everything that references an external source must be accessible to those users. If the source is a Network share or external database like SQL, that shouldn't be a problem. To do this:

1. Prepare the Query and save it as a Connection Only with or without it being loaded to the Data Model (presumably with in your case)
2. In the Queries & Connections pane, Right Click on the Query and select Export Connection File...
   

 

[jdellasala]

There is a way to publish a query to others, however everything that references an external source must be accessible to those users. If the source is a Network share or external database like SQL, that shouldn't be a problem. To do this:

1. Prepare the Query and save it as a Connection Only with or without it being loaded to the Data Model (presumably with in your case)
2. In the Queries & Connections pane, Right Click on the Query and select Export Connection File... View attachment 82505

CONTINUED (accidentally saved and ran out of edit time! Step 1 below continues from above):

1. The file will be saved to your default My Data Sources directory with a file name like Query - Table1.odc (Table1 is the actual name of the Query in this example. The rest of the filename is automatically generated). That directory should be under your default Documents directory. The file can be copied to a shared location or by email where recipients should save it to their default My Data Sources directory.
2. Users can access the file by going to Data -> Existing Connections.
   
   .
3. If they've saved it to their My Data Sources directory it will appear in the list, or use the Browse for More... button to get it from a shared location.
4. Once selected, they'll be presented with the standard Load To dialog box. If it's more than a million rows of data, I have no idea what happens if they select Table! Otherwise they'll have to select the appropriate option - most likely Only Create Connection and the Add this to the Data Model checked.
5. NOTE: If the Query relies on other Queries such as a Merge or Append, those queries will also be brought in!

Of course there's always copying the query in the Advanced Editor and pasting it into a Text file and sharing the Text file, but that's no fun!

 

[blimbert]

CONTINUED (accidentally saved and ran out of edit time! Step 1 below continues from above):

1. The file will be saved to your default My Data Sources directory with a file name like Query - Table1.odc (Table1 is the actual name of the Query in this example. The rest of the filename is automatically generated). That directory should be under your default Documents directory. The file can be copied to a shared location or by email where recipients should save it to their default My Data Sources directory.
2. Users can access the file by going to Data -> Existing Connections. View attachment 82506.
3. If they've saved it to their My Data Sources directory it will appear in the list, or use the Browse for More... button to get it from a shared location.
4. Once selected, they'll be presented with the standard Load To dialog box. If it's more than a million rows of data, I have no idea what happens if they select Table! Otherwise they'll have to select the appropriate option - most likely Only Create Connection and the Add this to the Data Model checked.
5. NOTE: If the Query relies on other Queries such as a Merge or Append, those queries will also be brought in!

Of course there's always copying the query in the Advanced Editor and pasting it into a Text file and sharing the Text file, but that's no fun!

Thank you so much for this response! I should have noted that I am operating Excel 2016. I am unable to find the "Queries and Connections" pane, only this workbook queries pane as attached. Further, in the Connections listed, I don't see a way to export as you mention in Step 2 (see the other attachment)

I assume you're using Excel 365 or something newer? Is there another route for Excel 2016? Thank you kindly.

 

[blimbert]

Thank you so much for this response! I should have noted that I am operating Excel 2016. I am unable to find the "Queries and Connections" pane, only this workbook queries pane as attached. Further, in the Connections listed, I don't see a way to export as you mention in Step 2 (see the other attachment)

I assume you're using Excel 365 or something newer? Is there another route for Excel 2016? Thank you kindly.

Additionally, if I right click on a query I do not see an option to Export it as you mention in Step 2.

 

[jdellasala]

I do not know what it currently says in the 2016 menus, but it is likely very close. In the Data tab, look for something like this:

Worst case, click buttons in the Data ribbon until the Queries appear.

 

[RoryA]

You'd use Save to Data Catalog in 2016 and share via Power BI. See more info here: Send Queries To Data Catalog in Microsoft Excel

 

[GraH]

Or... Copy query from query pane, open new workbook, open empty query pane and paste.
No need to open the advanced editor.