Building a SelectQuery Function

For a while now, I have been wanting to have the capability in Power Query to select a query by name PROGRAMMATICALLY.  Why?  Building a SelectQuery Function would allow me to execute one of multiple “Transform” queries depending on a user selection on the Excel sheet. This will help me process log files from multiple vendors which each have different contents and field names.

Here is a fairly simple example with only three input queries (although my true setup actually has seven potential queries to select from:

How the setup is intended to work:

I have a named range called “User_Select” on my sheet that has a Data Validation dropdown with the names of my source queries:

And what I want to do is read the value from the User_Select named range into my query named Selection_Query. This provides a scalar value that matches the query I’d like to execute.

The Issues

• I do not want to land the multiple input queries on sheets (too much data involved).
• I am not a fan of Power Query Parameters for this approach, as they must be changed from within the Power Query user interface (I don’t really want my users going in there.)
• I do not want to use “brute force” – I want to do this programmatically so that it is easy to maintain in future.

What is the “brute force” method?

The brute force method is essentially coding a great big IF/THEN statement that contains each possible query.  Looking at the M code, you’d end up with something like:

let
Source = if SELECTION_Query = "Query 1" then #"Query 1" else
if SELECTION_Query = "Query 2" then #"Query 2" else
if SELECTION_Query = "Query 3" then #"Query 3"
// (and so on, and so on, and shoobie-doobie-doo ?)
else null
in
Source

The problem however is that each time I create a new “Transform” for a new vendor, or retire it from production, I would also need to come back and update my brute force query to reflect these changes.  It would be MUCH simpler if I only had to add/remove the query name from my drop-down list, and not worry about messing with the M code of my Selector query.

Some hope for building a SelectQuery function

On 19 Feb, Gasper Kamensek presented a session at VANPUG’s Power BI track that got me excited. In his presentation, he showed how to programmatically select from some LANDED queries using the Excel.CurrentWorkbook() statement in Power Query:

let
Source = Excel.CurrentWorkbook({[Name=Selection_Query]}[Content]
in
Source

Enter Expression.Evaluate

Now, that worked great for items that had been landed to a worksheet table and got me thinking about this some more.  The challenge I’ve been facing is that I need to select from queries which were NOT landed to a worksheet table and therefore don’t show up via the Excel.CurrentWorkbook() function.  Wondering if this was even possible, I asked my friend Ken Puls. And guess what he Puls-ed out of his bag of tricks?

Source = Expression.Evaluate("some text string", #shared)

Now, I had encountered Expression.Evaluate() in the Power Query M function reference, but it was not clear to me what it was intended to do. But after Ken and I bashed this back and forth a bit… WOW!  Does this ever have potential!

Ken explained that Expression.Evaluate() works very similarly to Excel’s INDIRECT() function - it takes an input and tries to evaluate it at run-time.  Unlike Excel, which seems to just evaluate the provided term against any and all Excel items, Expression.Evaluate() requires you to specify the library you want to use to interpret the code.  And that’s where the #shared parameter comes in, as this parameter provides a list of not only all Power Queries in the solution, but also all of the available Power Query functions.

So Ken’s suggestion was to pass the name of the query I wanted in to the Expression.Evaluate function, and evaluate it against the shared library. At that point – he told me – it should give me the results of that query.

Armed with this theory, I was eager to plug it in to my SELECTOR query, which gave me this:

let
Source = Expression.Evaluate(Selection_Query, #shared)
in
Source

AND IT DIDN’T WORK. ?

Expression.Identifier to the Rescue!

Turns out, it’s not Ken’s fault – I like to name my queries with spaces and leading numbers.  After a little digging, it became apparent that the Expression.Evaluate() needed me to refer to “Query 1” with a pound sign and quotes.

In other words, this DOESN’T work:

=Expression.Evaluate(Query 1, #shared)

But this DOES:

=Expression.Evaluate(#"Query 1", #shared)

So now I just needed to figure out how to automatically “escape” the query with the #” “ requirement where necessary.  I suppose I could have put those into my Excel drop down, but that would make the list values look kind of ugly, so I went hunting something a bit more elegant.

After poking around in the M manual, I found Expression.Identifier(“some text”), and guess what it does? It converts the name we see in the Queries & Connections panel into the correct “# and quote” syntax.

So that gives me:

let
qName = Expression.Identifier(Selection Query),
Source = Expression.Evaluate(qName, #shared)
in
Source

AND IT WORKS!

Completing the Solution

To make this as flexible as possible (and allow me to use it in other projects), I decided that building a SelectQuery function was the way to go.  So here’s what I ended up with:

The fxSelectQuery function:

(qName) =>
let
Source = Expression.Evaluate( Expression.Identifier(qName) ,#shared)
in
Source

And at that point, I can invoke whatever query I need by passing the results of the Selection_Query to the fxSelectQuery function like this:

The Output Query:

let
Source = fxSelectQuery(#"Selection_Query")
in
Source

And the end result is that I select the query I want to run from an Excel data validation list, click update, and I’m done.  How cool is that?

One caveat that I should probably mention here is that you must disable the formula firewall in order to use this setup.  You can do this by going to Get Data -> Query Options -> Current Workbook -> Privacy ->  Ignore.

You can download the example file here if you’d like to see the results of building a SelectQuery function in action.