Home > Excel, Power Query > Follow Up of Chris Webb’s post of “Specifying Allowed Values, Sample Values and Descriptions For Function Parameters In Power Query” – Using Dynamic Value

Follow Up of Chris Webb’s post of “Specifying Allowed Values, Sample Values and Descriptions For Function Parameters In Power Query” – Using Dynamic Value

Another great blog post of “Specifying Allowed Values, Sample Values and Descriptions For Function Parameters In Power Query” from Chris Webb.

In his post, he showed a way to use a dropdown instead of a plain text box to prompt the user to select the value for the parameter of a Power Query function.

In his code between line 13 and 17:

MySecondNumberParamType = type number
                        meta
                        [Documentation.Description = "Please choose a number",
                         Documentation.AllowedValues = {1..5}],    

The allowed value 1,2,3,4,5 is hard-coded. I wonder can I use a Power Query function to dynamically populate the AllowedValues instead? So I did the following test:

  1. Create a single column table in Excel, looks like the blow.
    ID
    =============
    1
    2
    3


    10000
  2. Add this table to Power Query (Power Query ribbon –> From Table) and name this Power Query as “Table_Param”
  3. Change the column type to Text. This is important, see blow:
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID ", type text}})
    
  4. Go to Chris’s Power Query “MultipleTwoNumbersV2” and open the advanced editor
  5. Change the code from
    Documentation.AllowedValues = {1..5}], 
    

    to

    Documentation.AllowedValues = Table.ToList(Table_Param)],
    
  6. Now try to invoke this Power Query function and you will find the dropdown box having the value return form the function.

This dynamic value approach should enable you to get the data from various sources e.g. database, Excel, web etc

However, there is two limitations:

  • The data type in Table_Param must be Text. And this is highlighted in the above step 3. Therefore, you need to either perform an inline type cast or a change data type step in the Power Query.
  • Although the Table.Tolist() support large volume of data, the number of the allowedvalue populated in the dropdown box will only be first 1000 items. Well, in most cases, that’s more than enough.

 

 

 

 

Advertisements
Categories: Excel, Power Query Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: