Power Query: It’s Good to Share (a Query)

Welcome to our Power Query blog. This week, I take a look at exporting queries to other workbooks in Power Query or Get and Transform.

Regular readers will know that this blog is now constructed in Excel 2016 and therefore uses ‘Get and Transform’. In most cases, it’s easy to follow in earlier versions of Excel which have a Power Query tab instead, but there are some improvements to ‘Get and Transform’ that may tempt me into an upgrade. This is one of them. It is actually only available in Office 365 update. There are however other ways to share queries if this particular upgrade is not available, as I will describe later (below).

The functionality I am beginning with will only be accessible if, instead of a ‘Queries’ pane that appears on the right hand side of the workbook, there is a ‘Queries and Connections’ pane, which looks like this:

From the ‘Queries and Connections’ pane, I can right click on any query and export it.

Once the query has been exported to an ODC (Office Database Connection) file, it can be sent to colleagues. If I have an ODC file on my computer, I can use it by viewing it from the ‘Existing Connections’ screen from the ‘Data’ tab.

So, what if I don’t have the latest Office 365 update? There are some other ways to share queries.

I have a query shown below, which I think would be useful in another workbook. For this example, I’ve chosen to use Excel 2013. I will copy my query and paste it into my new workbook. Note that I have selected an independent query for this example.

My first step is to go to the Query Editor and select the ‘Advanced Editor’.

I need to copy the M language from the ‘Advanced Editor’:

In a new workbook, I create a new ‘Blank Query’:

In my new ‘Blank Query’, I access ‘Advanced Editor’ and paste in my M language:

I can then save my query and use it in the workbook. Note that if I had chosen a query that was dependent on other queries, I would have to copy them all across, and I would choose to do this in the order in which they were created.

There is another way to share queries. If I right click another query, I can see I have a ‘Copy’ action available. I am using Excel 2016 Professional Plus for this example, but there are versions of Excel 2013 with this option too.

I copy my query and then paste it into the ‘Query Pane’ of a blank workbook.

My query appears in my blank workbook.

Since this method creates a query, the checks are more stringent from the start: if the query refers to a table in the original workbook, then the query will have a warning icon next to it. The copy and paste method is simpler to use as a ‘template’ as a basis for a new query, as this method may copy more than one query – but there is a good reason for that. I choose a query that is not independent:

Then I paste this into a new empty workbook:

I don’t just get the query I have copied:

I get all my referenced queries too, which is much more efficient for dependent queries that I need to get a number of other queries for. Therefore, for those of us that don’t have the ability to export ODC files (yet!), these can be useful methods of sharing popular queries.

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here.