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:

Illustration of the query chain with three queries that pull from a single data source, and another query that feeds the SelectQuery function to choose which to execute

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.

Major redesign at Skillwave.Training

This past weekend we published a major redesign at Skillwave.Training.  Months in the making, this has been a total overhaul to focus on delivering the best online learning experience for our clients.  Check out some of the images from the new site:

Centralized Dashboard

When you log in, you’ll be taken to your Dashboard immediately.  This is the one stop console that will let you access any of your active course subscriptions, review forum posts, download your files, and manage your billing and profile details.  We’ve worked hard to make this dashboard intuitive and easy to use as possible, and to make it look great on mobile as well.

Re-Designed Course Player

The course player is a completely custom built as well.  Of course, you’d expect to see your navigation menu on the left to get to your lessons, but we’ve also added a “Materials” fly out menu on the right where you can access files specific to any given lesson.The Materials flyout in action in the Skillwave Course Player

Community Forum Overhaul

We said is was a major redesign at Skillwave.Training, and we meant it.  One of our big goals here was to do a better job with the Skillwave help forum and foster a sense of community within it.  Our belief is that learning is great, but there can be another hurdle when trying to convert theory into practice with your own data.  We see the forum experience and Skillwave community as a crucial part of solving this issue, giving students the ability to:

  • Ask questions about the course materials,
  • Get help with applying techniques to their own data,
  • Interact with other people in the same training,
  • Practice applying their skills to other data sets, and
  • Reinforce their knowledge and help others in the process.

Any of our clients who have an active subscription to one of our paid products will find a completely revamped forum experience.  As forum posters ourselves, there were a couple of very important things that we wanted to make sure that our community was provided a good set of tools for:

  1. Asking To this end, we’ve made sure that we support topic tags, image and file uploads, code tags and a variety of rich formatting options.  (Our old forum was quite weak in this regard).
  2. Answering In addition to the tools above, we’ve added the ability to mark questions as solved. Our forums are searchable based on topic tags, answered status, solved status and more.
  3. Ensuring high quality answers. Our forum is private and monitored by our admin team.  Even if Matt, Miguel or myself aren’t the ones answering specific questions, we have a special “Recommended Answer” tag that we can apply to answers.  This serves two purposes to us: the first is providing assurance to the asker that they got a great answer, while the second is providing validation to a poster that they’ve provided a high-quality response.

Course to Question Integration

There’s one more really cool thing though… We also now give you the ability to post a forum question directly from a given lesson and provide links to all other questions that have been posted in this manner.  This serves both askers and answerers as it links directly back to the source of the question.  We’re super proud of this little feature and feel that it sets us apart from other platforms out there.  Not because other platforms don’t offer the ability to ask questions – they do.  But we serve all of that up right inside the lesson page.

A demo of the integration from course player and our forum

Check Out the major redesign at Skillwave.Training

If you haven’t checked out Skillwave.Training yet, you really should.  We’ve got all kinds of great courses related to Excel, Power BI, Power Query and DAX.  You can even try out the platform via our free Power Query Fundamentals course.  You won’t have access to the forums on the free tier, but you’ll be able to experience the rest of our new platform.

As we've just launched the site, we'd love to get your feedback.  For the next month or so, you can do that by clicking the little Feedback widget on the right side of any site page.  Let us know what you think!The feedback widget in action on Skillwave.Training

Free ‘DIY BI’ e-Books

Today I wanted to just make a quick announcement that we are currently working on a series of free 'DIY BI' e-Books.

Free 'DIY BI' e-Books? Tell me more!

Over the past few years of working with Excel an Power BI, I've obviously picked up a few different methods, tips and tricks for working with the software.  And looking at how successful our free e-Book "Magic Tricks for Data Wizards" has been through the Power Query Training site, I thought it would be nice to so something similar for Excelguru readers.

One of the cool things about the Excelguru audience at this site is the diversity.  A lot of people originally came here for Excel, but we've been exploring Power Query, Power Pivot and Power BI for the past few years as well.  The one thing that ties us all together is that we are building "Do it Yourself Business Intelligence" or "DIY BI".

My original plan was to release one e-Book with 20 different tips, tricks and techniques; 5 each for Excel, Power Query, Power Pivot and Power BI.  After getting started, however, I realized that it was going to take me a bit longer to get that all done than I wanted.  But since I want to get information out to our readers, I've decided to break this down into four separate e-Books which will be collected under the umbrella of "DIY BI Tips, Tricks and Techniques".  Each e-Book will focus on one specific area of the DIY BI story.

What will the free 'DIY BI' e-Books include?

Well… tips, tricks and techniques, of course.  Smile  Okay, seriously, each is fully illustrated and written to give you some great examples and ideas that I hope will help you in your DIY BI journey.

Here is what is covered in DIY BI Tips, Tricks and Techniques for Excel:

  • The easiest formula to return the end of the month
  • Show a message when cells are hidden
  • Quick alignment of objects
  • Easy to read variances
  • Show a message if your Pivot data is stale

Sample image from DIY BI Tips, Tricks & Techniques for Excel

What areas will the free 'DIY BI' e-Books cover (and when will they be released)?

Those e-Books will be released in the following order:

  • DIY BI Tips, Tricks and Techniques for Excel
  • DIY BI Tips, Tricks and Techniques for Power Query
  • DIY BI Tips, Tricks and Techniques for Power Pivot
  • DIY BI Tips, Tricks and Techniques for Power BI

The first is already written, we just need to lay it out and make it look a bit more awesome.  Our target is to get it released by the end of next week.

With regards to the rest, I'll go as fast as I can on them, but as you can imagine, doing things right does take time.  I would expect that each will take 2-3 weeks to build out properly, but if I can get them out faster I most certainly will.

Am I going to need Excel 2016 to get value from the free 'DIY BI' e-Books?

No.  While I highly advocate being on a subscription version of Excel 2016, you'll find content in each of the first three e-Books which can be used in prior versions of Excel.

How do I receive the free 'DIY BI' e-Books?

You sign up for the Excelguru newsletter.  It's just that easy.  As soon as each e-Book is finished, we'll be emailing it to everyone who is currently subscribed to our newsletter.

And in the mean time, you also get a monthly email from us which now includes news about the latest updates to both Excel and Power BI.

Longer term, once all four e-Books are written, any new subscribers will receive the first e-Book upon signup, and then the next in the series will arrive every couple of days until you have the full set.

So what are you waiting for?  Sign up right here and don't miss out on free DIY BI Tips, Tricks and Techniques for your work!

Subscribe to our mailing list

* indicates required