Search code examples
powerquerypowerbi-desktopm

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.

Solution

  • 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.