Custom Columns Can Be Setup to Dynamically Feed Their Value Directly Into Any User Variable

A couple of years ago, the Power Query team added Parameters as a proper object, but I kept on Creating Dynamic Parameters in Excel Power Query the same way as I always had.  The reason for this is two-fold: the first is because I was used to it, the second was because the built-in Parameters are quite static.  Sure, you can set up a list and change them at run time, but you have to enter the Power Query editor to do that.  And is that really something you want your boss doing?

So why do we care about creating dynamic parameters, anyway?

Let's take a look my last technical blog post to understand this.  In that post, I pulled a Top 5 value from an Excel cell, and used that to drive how I grouped my items.  It works great, and is truly dynamic.  It puts control of the grouping in Excel, allowing a friendly user interface for the end user to work with.  They simply change a cell value, hit refresh, and all is good.

The challenge here is not from the end user's perspective, it's from the developer's.  One of the instructions I gave in the post last week was to:

• Create a Custom Column using the following formula:
  • if [Rank] <= TopX then [Item] else "Other"

Why a Custom Column?  Why not just use the Conditional Column dialog?  The answer is simple… TopX in this case was a query that returned a value, but it was not a proper Power Query Parameter.  Does it work the same in code?  Pretty much yes, but you can't see it in the Conditional Column dialog as you're building the query.

Even worse, if you want to make any modifications to the logic, you have to do it in either the formula bar or the Advanced Editor, as the gear icon returns the conditional column builder, but can't resolve the query:

Wouldn't it be nice if we could create dynamic parameters that actually show up as valid Parameters to Power Query?  That's the goal of this post.

Groundwork - Creating the dynamic parameters in Excel

There are two different ways we can do this:

 1. Fetching dynamic parameters using a Named Range

This is the super easy method.  To do this:

• Enter your parameter value in a worksheet cell
• Go to the Name Manager and define a name for the cell (I called mine rngKeep)
• Select the cell and pull the data into Power Query
• Right click the value in the table's cell --> Drill Down
• Rename the query
• Load it as a Connection only

For this example, I renamed my query to XL_TopX_NamedCell.  It's a long name, I know, but you'll see why in a bit.

2. Fetching dynamic parameters from a Parameter Table using the fnGetParameter function

I've detailed this technique on the blog before, so if you'd like to review this technique, you can find a detailed post on that here.  The quick summary:

• Create a two column table called Parameters, with Parameter and Value columns

• Copy in the fnGetParameter function (from the other post)
• Call the function as needed

Just to check the value, I then:

• Created a blank query
• Entered the following formula in the formula bar
  • =fnGetParameter("Keep top")
• Named this query XL_TopX_fnGetParameter
• Loaded it as a connection only

So what makes a parameter a "Real" parameter?

At this point, I decided to create a new parameter and look at what happens.  To do this, go in to the Power Query editor and…

• Go to Home --> Manage Parameters --> New Parameter
• Give the Parameter a name (I used Test)
• Set a Current Value of 0
• Click OK

Next, right click the Parameter in the Queries pane on the left and go to the Advanced Editor.  You should see code that looks like this:

0 meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

So this is interesting… 0 is the value, and the rest is just a meta tag to tell Power Query that this is a real parameter…  This got me wondering… am I stuck with this value, or can I feed it a Power Query code and actually create a dynamic parameter that updates at run time?

Converting a Query to a dynamic Parameter - Take 1

The first thing I did here was copy everything after the 0, then exited this query.  I then:

• Jumped over to the XL_TopX_NamedCell query
• Entered the Advanced Editor
• Pasted the copied line of code at the end
• Clicked OK

And it didn't work.  Not to give up, I jumped back into the Advanced Editor and wrapped the original query in parenthesis like this:

And this time, something did change:

There are 3 things worth noting here:

1. It has the parameter icon (Yay!)
2. It doesn't show a current value but shows an exclamation icon
3. It shows the value of (…) in the name - meaning it doesn't know what the value is

I wasn't too worried about this last one though.  Dynamic named ranges show the same way in Excel, so would this work to create dynamic parameters?

It sure does!  Not only does it show up in any parameter drop down, but the value gets read correctly and allows me to make my comparisons.  How cool is that?  I've actually got a dynamic parameter now!

Converting a Query to a dynamic Parameter - Take 2

Now, as cool as this was, there is something that bothered me about it.  When you tag the meta data at the end of a functional query and turn it into a parameter, you lose the applied steps.  If anything goes wrong, it makes it hard to debug.  (Reminds me of the classic custom function setup.)

To solve this, I decided to remove all the meta tags and parenthesis from the XL_TopX_NamedCell query, returning it to what is was before I turned it into a parameter.  I then created a new blank query called TopX_NamedCell and edited the code in the Advanced Editor to read as follows:

XL_TopX_NamedCell meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

Why?  Because I now have the query that pulls in the original data.  When I click on it, I can see the values and debugging steps to get there:

And I also have a Parameter, which pulls from this value and can be used in my drop downs:

Extending dynamic Parameters to leverage the fnGetParameter function

If you've used the fnGetParameter function before, it only makes sense that you'd want to know if we can leverage this function to pull values and return real Parameters.  And indeed you can.

 Parameters that pull from fnGetParameter

Here's the quick and dirty way to create dynamic Parameters by calling the fnGetParameter function directly:

• Create a new blank query
• Name your new Parameter  (I called mine TopX_DirectFromFunction)
• Go into the Advanced Editor
• Paste in the following code:

fnGetParameter("<Variable Name>") meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

• Replace <Variable Name> with the name of the variable you want from the Excel Parameter table.  In the example this would be fnGetParameter("Keep top")
• Click OK

Yes, it's just that easy.  You've now got a fully functional and dynamic Parameter… at least, you do if you replaced the variable name correctly with one that exists in the Parameter table!

NOTE:  I recommend that you rename your query before you edit the M code since you lose the applied steps window during the process.  You can still rename a parameter, but you'll need to right click it in the queries pane on the left and choose Rename to do so.

Making dynamic parameters that pull from fnGetParameter auditable

There's only one problem with the above approach. How do you test the value is resolving correctly before you try to use it?  Or how do you look to see what is actually happening when your downstream queries return an error?

For this reason, I actually recommend that you don't use the fnGetParameter query in a real Parameter as outlined in the previous section.  What I recommend you do is create an intermediary query which leverages fnGetParameter to pull the value from the Excel table, then reference that query from the Parameter query.  So in short:

This is also fairly easy to set up.  The full process would be:

• • Copy in the fnGetParameter function
  • Set up the Parameters table in Excel and populate it with data
  • Create a new blank query to retrieve the parameter value
    • Name it
    • Enter the following in the formula bar:
      • =fnGetParameter("<variable name>")
      • replace <variable name> with the name of the parameter you wish to retrieve
    • Load as Connection only
  • Create a new blank query to be the real Parameter
    • Name the parameter as you'd like to see it in drop down lists
    • Go into the Advanced Editor and enter the following
      • QueryName meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]
      • Replace QueryName with the name of the query you created above
    • NOTE: Parameters will automatically load as Connection Only queries
  • Use the new Parameter in other queries

See it in action…

The attached sample file contains three different variables based on the methods above, any of which can be used to drive the Conditional Columns step in the Grouped query:

And if you're curious, they are related as shown below.  The TopX_NamedCell parameter is driving the version I saved, but as per the above, you can change that out easily.  (Naturally, in this case they all serve up the same value though!)

Some Observations

As I was playing around with this, I noticed a couple of things that are worth bringing up here.

Yes, these work in the Power BI service!

To test this out, I cooked up a small sample that used a dynamic parameter using the methods outlined above to read the most recent year's data from a SharePoint folder.  I then published it to the Power BI service, added a new file to the server and refreshed the data in Power BI online.  Worked like a charm.

For the record, I haven't tested, but don't anticipate that this will work well with Power BI templates, as they will most likely clear the parameters and prompt you for values.  Any data points you wish to be preserved should be left as queries.

The Convert to Parameter function

Assume you created a new query, then typed a value into the formula bar (not a formula, but it could be numeric or text).  This would return a single (scalar) value that is static.  You'd then be able to right click the query in the Queries pane and choose Convert to Parameter.  Unfortunately, if your query returns anything that is dynamic or has multiple data points, this option is greyed out.  That's too bad, as this would be a really cool thing to be able to do.

Avoid the Add/Manage Parameter UI

Unfortunately, adding even a single dynamically-driven parameter renders the Manage Parameter dialog useless to you.  The reason is that as soon as you try to say OK to any parameter in that list (whether modifying or creating a new one), it appears to try to validate the current value of each of the listed parameters:

This is unfortunate, as it means that you'd need to kick over to a blank query to create any new Parameters or debug any existing ones.

UPDATE:  Thanks to Andrew in the comments, I know that you can uncheck the Required value when creating your parameter.  If you do that the M code upon the initial creation comes up as:

0 meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=false]

If the required setting is false, then the manage queries dialog can still be used without forcing an update!

 The Parameter meta tag

The only part of the Parameter meta tag that is actually required is the following:

meta [IsParameterQuery=true]

Having said that, I got mixed results doing this.  Sometimes the Parameters were not presented in my drop down list.  Editing those queries and restoring the full meta tag to the end resolved that immediately.  I.e.:

meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

fulmertragstenk.blogspot.com

Source: https://excelguru.ca/creating-dynamic-parameters-in-power-query/

Share this post