CODEWITHSUNDEEP
powerquerypowerbi-desktopm
KvothesLute
KvothesLute

Reputation: 195

Parameter options for User Defined Functions in PowerQuery

Hi i have been trying to make a user defined function that allows the user to select the values which the function will use from a list.

I have tried setting the parameter i want as a list to type list in my function but this only seems to accept columns rather than a list of values a user can select from.

let
ListOfDays = {1.1,0.5,2,3,1},
DayOfTheWeek = (Day as list, HoursWorked ) =>
 let
    Earnings = Day * HoursWorked
 in
    Earnings

in
DayOfTheWeek

What i would like is for me to allow the user to select a single value from the ListOfDays list. I used typed list within my function parameters so that it can give the user a dropdown list kind of option.

Upvotes: 1

Views: 1622

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40244

I believe this is the relevant documentation you are looking for: github.com/microsoft/DataConnectors/docs/function-docs.md: Adding Function Documentation

In particular, look at the definition for Documentation.AllowedValues:

List of valid values for this parameter. Providing this field will change the input from a textbox to a drop down list. Note, this does not prevent a user from manually editing the query to supply alternate values.

This (and other Documentation fields) are part of the meta typing of the function arguments. Scroll down to the code snippet which shows how to use them:

[DataSource.Kind="HelloWorldWithDocs", Publish="HelloWorldWithDocs.Publish"]
shared HelloWorldWithDocs.Contents = Value.ReplaceType(HelloWorldImpl, HelloWorldType);

HelloWorldType = type function (
    message as (type text meta [
        Documentation.FieldCaption = "Message",
        Documentation.FieldDescription = "Text to display",
        Documentation.SampleValues = {"Hello world", "Hola mundo"}
    ]),
    optional count as (type number meta [
        Documentation.FieldCaption = "Count",
        Documentation.FieldDescription = "Number of times to repeat the message",
        Documentation.AllowedValues = { 1, 2, 3 }
    ]))
    as table meta [
        Documentation.Name = "Hello - Name",
        Documentation.LongDescription = "Hello - Long Description",
        Documentation.Examples = {[
            Description = "Returns a table with 'Hello world' repeated 2 times",
            Code = "HelloWorldWithDocs.Contents(""Hello world"", 2)",
            Result = "#table({""Column1""}, {{""Hello world""}, {""Hello world""}})"
        ],[
            Description = "Another example, new message, new count!",
            Code = "HelloWorldWithDocs.Contents(""Goodbye"", 1)",
            Result = "#table({""Column1""}, {{""Goodbye""}})"
        ]}
    ];

HelloWorldImpl = (message as text, optional count as number) as table =>
    let
        _count = if (count <> null) then count else 5,
        listOfMessages = List.Repeat({message}, _count),
        table = Table.FromList(listOfMessages, Splitter.SplitByNothing())
    in
        table;

They also provide a screenshot of what this should look like when invoked:

Screenshot

Upvotes: 2

Alexis Olson
Alexis Olson

Reputation: 40244

If the user is able to open up the Query Editor, then they can choose a Day parameter from a dropdown list and have this automatically apply to the query.

You would create the parameter from the Manage Parameters > New Parameter menu

Manage Param

The drop-down at the upper right of the image is how the user would select the choice.

Your User Defined Function fn_DayOfTheWeek would be the following:

let
   DayOfTheWeek = (Day as number, HoursWorked as number) =>
   let
      Earnings = Day * HoursWorked
   in
      Earnings
in
   DayOfTheWeek

Note that Day is a number, not a list. You want to choose from a list, not pass a list into the function.

Now you can invoke your function with your parameter to actually produce a result.

let
    Source = fn_DayOfTheWeek(Day, <HoursWorked value here>)
in
    Source

This result will update when you change the parameter.

As you can see, whether a user has access to the Query Editor is rather a critical question for this approach. I'm not sure if it's possible to somehow set a parameter directly within a custom connector dialog box or not but this should be equivalent in functionality.

Upvotes: 1

Related Questions