germaboss.blogg.se - Excel 2016 query editor manage parameters

Excel 2016 query editor manage parameters code#

Why? Because I now have the query that pulls in the original data.

Excel 2016 query editor manage parameters code#

I then created a new blank query called TopX_NamedCell and edited the code in the Advanced Editor to read as follows: 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. (Reminds me of the classic custom function setup.) If anything goes wrong, it makes it hard to debug. When you tag the meta data at the end of a functional query and turn it into a parameter, you lose the applied steps. Now, as cool as this was, there is something that bothered me about it. How cool is that? I've actually got a dynamic parameter now! Converting a Query to a dynamic Parameter - Take 2 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. Dynamic named ranges show the same way in Excel, so would this work to create dynamic parameters? I wasn't too worried about this last one though. 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: Pasted the copied line of code at the endĪnd it didn't work.Jumped over to the XL_TopX_NamedCell query.The first thing I did here was copy everything after the 0, then exited this query. 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.