It shows the value of (…) in the name - meaning it doesn't know what the value is.It doesn't show a current value but shows an exclamation icon.Not to give up, I jumped back into the Advanced Editor and wrapped the original query in parenthesis like this:
#VBA EXPECTED NAMED PARAMETER CODE#
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 You should see code that looks like this:Ġ meta
Next, right click the Parameter in the Queries pane on the left and go to the Advanced Editor. Give the Parameter a name (I used Test).Go to Home -> Manage Parameters -> New Parameter.To do this, go in to the Power Query editor and… So what makes a parameter a "Real" parameter?Īt this point, I decided to create a new parameter and look at what happens. Named this query XL_TopX_fnGetParameter.Entered the following formula in the formula bar.Copy in the fnGetParameter function (from the other post).Create a two column table called Parameters, with Parameter and Value columns.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. Fetching dynamic parameters from a Parameter Table using the fnGetParameter function It's a long name, I know, but you'll see why in a bit. Create a Custom Column using the following formula:įor this example, I renamed my query to XL_TopX_NamedCell.One of the instructions I gave in the post last week was to: The challenge here is not from the end user's perspective, it's from the developer's. They simply change a cell value, hit refresh, and all is good. It puts control of the grouping in Excel, allowing a friendly user interface for the end user to work with. In that post, I pulled a Top 5 value from an Excel cell, and used that to drive how I grouped my items. Let's take a look my last technical blog post to understand this. And is that really something you want your boss doing? So why do we care about creating dynamic parameters, anyway? 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. 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. 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.
0 kommentar(er)
