Creating Dynamic Parameters in Power Query

2018-05-30
Ken Puls
Categories: Excel, Power BI, Power Query

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:

It has the parameter icon (Yay!)
It doesn't show a current value but shows an exclamation icon
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]

Tags: dynamic, function, parameter, power bi, power query

29 thoughts on “Creating Dynamic Parameters in Power Query”

Wouter De Raeve on May 30, 2018 at 12:42 pm said:

Hi Ken,

interesting approach to 'internalize' external parameters.
It makes the usage of them a lot more convenient.

Maybe a bit off topic, but I was hoping you meant the following with dynamic parameters...
What I find really annoying, is that when you link a parameter to a list of values, this list of values does not get updated unless you go into the 'Edit Query'-window.
For example: you have a parameter that links to a list of countries. That list is for example a distinct select from the customer table on a sql server.
Using that parameter you filter for example that same customer table.
However, when a new country is added that was not there before, you will not see it in the list. You first have to edit the list-query, refresh the preview, and then the new country will show. So basically, the parameter uses a list that is static, not dynamic. I have not found the way to first refresh the list query, and then the other queries (in Power BI Desktop, that is(...
Andrew L on May 30, 2018 at 2:51 pm said:

Hi Ken
Great post.
You will be pleased to know that dynamic parameters do not have to render the manage parameters window useless.
Simply mark your dynamic parameters as not required (unchecked the required box when first creating the parameter) and then enjoy being able to add and edit your static parameters without breaking your dynamic parameters.
Ken Puls on May 30, 2018 at 4:27 pm said:

Andrew, that is AWESOME. Thanks!
Ken Puls on May 30, 2018 at 4:28 pm said:

Honestly, neither have I, mainly because I've been unhappy with the static nature of a parameter. Based on the above, what about making a query that creates a list, then filling that list to the parameter? The query should be dynamic then. Would that work?
Wouter De Raeve on May 31, 2018 at 4:09 pm said:

That's exactly what I do now, but with the unfortunate behaviour. I'll send you an example...
Pingback: Power BI colors, data models, release notes and more... (June 4, 2018) | Guy in a Cube
Jeff on June 22, 2018 at 7:17 am said:

Thank you so much! Never new about this meta tag. I was trying to create a parameter as Type="Table", for which I could use in a table that I can create a function from . That way, I could have a model query that I could use to control the function with. Now I know how to do it. Thanks again!
Steve Myles on June 29, 2018 at 2:20 am said:

Ken,
Thank you for this really useful post. I have followed your recommendations and now have useful dynamic parameters. I am unable to get them to work in the Source applied step. Would you expect them to work in a source step or is this their normal behaviour ? When I use static Parameters in the source step they work - i am opening excel files on my computer
Ken Puls on June 29, 2018 at 8:13 am said:

Hi Steve,

Do you mean that they are not showing up in drop downs and such? To be honest, I didn't try using them in a Source step, but I would have expected them to either work or not, not be selective about where...
Steve Myles on July 3, 2018 at 4:55 am said:

Ken,
To clarify the dynamic parameters do show up in the drop downs and you can choose them but you get the following message
Formula.Firewall: Query 'ConnectToDailyAllocation' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
If I choose a static parameter it will work without this problem
Ken Puls on July 3, 2018 at 10:12 am said:

Ah, yes, sorry. I was reading on this over the weekend, and trying to combine data from two sources in one statement (like a file path and the file) in a single step causes this issue. Once you have the data in (past the source step), then you should be fine.
Albertan on February 1, 2019 at 7:53 pm said:

Hello, thanks for your post. I’m trying to come up with a statement when I need two or more records as criteria. If one record we use {0} in a statement. What if I have two rows I need to use for criteria, i.e. both {0} and {1}. Or bigger range. Thank you so much

Reply
David on April 2, 2019 at 5:46 am said:

I am creating an RDS Design Sheet form for people to complete using Data Validation, part of which involves merging, data tables into a view using some of the techniques above. For example the designer chooses a validated version of SQL from a drop down list and in another selects the Edition, e.g. SQL 2016 & Express edition. Those 2 values form the basis for power BI dynamic parameters, which then filters the Allowed Machine Type
I have all the tables and Power queries working, so thanks for that. Only issue is the option to refresh the dataset generated by the parameters from the named ranges, does not automatically update the power BI result set. Only works if you hit refresh all - which is a bit clunky.
Ken Puls on April 2, 2019 at 5:04 pm said:

Hi David,

Not sure what to say here... the way the Power Query stack is not live in either Excel or Power BI - they both require a refresh to work. It's just the way the tool was designed...
Adrian on August 25, 2019 at 7:51 pm said:

Thanks for this!
Philip on November 14, 2019 at 8:40 am said:

Hi Ken,

This is a really useful article, but I'm having an issue that I can't work round and would be really grateful if you could point me in the right direction.

In your sample sheet, when you enter a new value in the cell 'rngKeep', it reflects immediately in the preview windows when you hover over the queries in the Queries & Connections panel. It does the same when I add a new parameter ('Keep bottom') to your Parameter table. I.e., it's truly dynamic.

However, when I try the same thing in a new workbook, following the steps you outline, the query values don't change when I change the cell value. I have to go in and refresh the preview to force the value to change.

I've looked at the queries in the Advanced Editor and the Query Properties and they look entirely consistent. I've even tried looking in the constituent XML files for the workbook and, at least for the queries themselves, they look equivalent.

I have both your sheet and mine open at the same time, so it doesn't look like it's anything to do with the way Excel itself it configured.

Any idea what I might be missing or doing wrong?

Many thanks.
Ken Puls on November 14, 2019 at 10:30 am said:

Hi Philip,

You’re not doing anything wrong. Power query caches previews, so that’s totally normal. When you refresh the data in normal use it will read properly.

The weird part is that the preview doesn’t necessarily get updated when you do that, so things look old when you are debugging as the data may be current but the preview still shows old values. My rule of them whenever I debug anything is to open the editor and click refresh preview before doing anything else.
Philip on November 19, 2019 at 9:00 am said:

Thanks Ken.

It's bizarre. A 'production' spreadsheet and a test sheet I created last week (the latter on a new-build machine) didn't show the changed cell value when I hovered over the query in the side panel. The one I created today on the same machine does. And as far as I know, nothing else changed in the mean while.

I will just have to write it down to a Microsoft quirk!

Thanks again.
Jacek on November 21, 2019 at 10:33 pm said:

Hi Ken,

your method is ok but when you are providing fngetParameter into advance editor:

let
Path = t_Parameters,
Source = Excel.Workbook(File.Contents(Path), null, true),
t_ips_Table = Source{[Item="t_ips",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(t_ips_Table,{{"ID", type text}, {"IP", type text}, {"Subnet", type text}})
in
#"Changed Type"

and creating in next step power pivot - Excel is unable with VBA to RefreshAll.
It stoppes after first query and not executing further queries and codes.
Did you have similar issue?

Best,
Jacek
Ken Puls on November 28, 2019 at 2:47 am said:

Hi jacek,

I can't say that I've had issue here, no...
Jacek on December 4, 2019 at 1:52 am said:

Hi Ken,

It is very strange behaviour. I read that if you have pivot tables using dynamic parameters it can be an issue for refreshing....

I didnt find any solution for this. Maybe i should take parameter in other way.
Best,
Jacek
Lucas on April 15, 2020 at 4:02 am said:

Amazing solution, I was burning my brain off trying to make something like this. Works like a charm. Thank you very much 🙂
Andres Mira on April 22, 2021 at 10:40 am said:

Hi Ken!! Greeetings From Colombia....

This solution is amazing... finaly I found this blog and could could resolv my problem

Thankssssssss
Gavin on January 11, 2023 at 6:09 pm said:

I love the solution above, but it has only got me 95% of the way to what I require. I am wondering if you can help.
I am populating an XL Table from an XML link using
Xml.Tables(Web.Contents("https://address.io/" & Company & "/Branch")).
"Company" is a parameter. I can manually set Company to a value and the table populates correctly but I need it to be dynamic. I have followed what you suggested and have a query that links to an XL range and returns the required value. Then modified my paramater to get the result from the query.
qryCompany meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]
In short the Company parameter is not returning a value and the xml query fails. Help!
Ken Puls on January 12, 2023 at 9:15 am said:

Hi Gavin,

To be honest, I would skip using the official Parameters for this. It is too much overhead and too fragile. Instead, I would set up your Excel table (as you already have) with individual cells for your Company and Branch, but then add one more Excel cell that joins them into a single item called "URL". Read that using the fnGetParameter function into Power Query and you're going to find life is a lot easier.
William on February 20, 2023 at 5:59 am said:

Hi
I have a parameter used in the power query connection into which I enter a year e.g. 2020 and I wish to reference this year in an excel sheet.
Could you please help me.
Thanking you
William
Ken Puls on February 20, 2023 at 9:59 am said:

Hi William,

If I understand correctly, you've got the parameter set up in Power Query as a Parameter, and you want to read that out into an Excel cell? I'll admit that this is usually the opposite of my needs (typically I have the value in a cell and need to pull it into Power Query.)

Regardless, it shouldn't be hard. Go into the PQ Editor, right click the parameter -> Reference. Then on the ribbon find the command to convert it to a table (using the default options). Rename the column and load it to the worksheet.
Melanie on August 21, 2023 at 4:44 am said:

Hi - I tried to use this with a sharepoint URL as the parameter in a SharePoint.Contents function, but I get "DataFormat.Error: Invalid URI: The hostname could not be parsed." Is this a limitation around using a parameter in that function, or is it possible and I'm just doing something wrong?
Ken Puls on August 22, 2023 at 8:35 am said:

Hi Melanie,

SharePoint connectors require connecting to the root of the SharePoint site/library and drilling down to the subfolder. To further complicate matters, the method to do this via SharePoint.Files and SharePoint.Contents is different.

Today, by far the easiest way to do this is actually to install my Monkey Tools add-in. On a free license, insert the "SmartFolder" function. Documentation on how to implement this function can be found under Option 2 on this page: https://monkeytools.ca/smartfolder-function/. (On a Pro/Trial license, you can use the SmartFolder Monkey, which will insert the updated version of fnGetParameter, the table, the SmartFolder function and hook it all together.)

Note that anything you create with Monkey Tools will continue to work even if someone does not have Monkey Tools installed.