MS Power Query: Sharing Queries across workbooks and xlam?

1
votes

In Power Query, is it possible to build a query in one workbook and access it from another? In particular, can I build a whole set of queries into some kind of library? I'm thinking of, say, an .xlam file and then have multiple .xlsx files load the .xlam and use the queries

I really want to stay away from copy-and-pasting query-definitions from one workbook to another. It's not maintainable for even medium-sized projects.

I've read this post and really appreciated @dfresh22 function for automating the copying of queries. But that's still not there.

I tried creating a query in a workbook, saving it as an .xlam, and referencing the xlam from a second workbook. But I couldn't find any way to access the query.

I'm running Excel 2013.

Am I just thinking about this the wrong way?

2
excelpowerqueryxla

2 Answers

1
votes

One approach is to store the queries in a file somewhere, use File.Contents to access that file, and then use Expression.Evaluate on the contents of that file, as mentioned in this blog post. The step would look something like this:

Expression.Evaluate(Text.FromBinary(File.Contents(file_path_to_file_with_queries)), #shared)

#shared is a special value which returns a record of all of the queries and library functions.

There are a some problems with this approach:

  • Certain types of static analysis, such as that used for finding data sources in the current file, will not work for the queries loaded from the file
  • The file contents can change, which can break your queries or connect to sources you don't know about.
  • The workbook is less portable because the queries don't travel with it. Whoever you share your workbook with must have access to the text file with your queries.
0
votes

You also can store queries and functions at github. see example here: https://github.com/tycho01/pquery (file named Load.m)