Describing a Function’s Record Parameters

Did you know that Power Query provides a way to define field-by-field details for a function’s record parameters?

You may already be familiar with how metadata can be used to provide descriptive details about a function and its parameter list. When provided, Query Editor uses this information to enhance the function invocation forms it generates and the IntelliSense it displays.

However, for record parameters, what’s not so well known is how to go beyond simply describing the basic details of a parameter to detailing its expected shape—such as the list of fields the record may (or must) contain, a friendly name for each field and even the allowed values on a per-field basis.

Pulling this off is easy!

Specifying the Shape

Say you have a function which accepts a record:

(input as record) => …

By default, the auto-generated function invocation form indicates that a record should be passed in but doesn’t give any indication about what that record should contain. (In fact, as of this writing, the invoke function form doesn’t even offer a way for you to input the record.)

Let’s rectify this lack of detail by ascribing a new type to the function. In that type, instead of setting the function’s argument type to plain record, set it to a custom record type that specifies the shape of the expected record.

let
  Func = (input as record) => …,
  NewType = type function (
    input as 
      [
         Server = text, 
         optional Port = number,
         optional CutOff = date
      ]
    ) as any,
  Ascribed = Value.ReplaceType(Func, NewType)
in
  Ascribed

Notice the change in the UI. Now we have the ability to use the function invocation form to provide values for the record argument’s various fields. The input box for each field respects the field’s data type (so the Port field only allows numbers to be entered, CutOff offers a date picker input control, etc.). The “Invoke” button won’t execute until values are provided for all required fields. Optional fields are noted with an “(optional)” after their names. A vast user experience improvement, all simply because we ascribed a shape-defining type!

Enhancing the Display

But even more control over this form is possible.

Several of the function parameter documentation metadata attributes also work for a parameter’s record fields. These can be used to control the UI’s display even more.

let
  Func = (input as record) => ...,
  NewType = type function (
    input as 
      [
        Server = (
          type text meta
          [
             Documentation.SampleValues = { "localhost"}
          ]
        ), 
        optional Timeout = (
	      type number meta 
	      [
	        Documentation.FieldCaption = "Time Out [in seconds]",
	        Documentation.AllowedValues = { 10, 60, 360 }
	      ]
	    ),
	    optional CutOff = date
	  ]
	) as any,
  Ascribed = Value.ReplaceType(Func, NewType)
in
  Ascribed

Notice how the first of Source‘s SampleValues is displayed inside its input box as its example text (“Example: localhost”). Timeout‘s field caption is displayed above its drop down box, causing it to appear in the UI as “Time Out [in seconds]” while the drop down choices offered are defined by the field’s AllowedValues list.

Annotating a function’s record parameters with details about their expected shapes significantly improves the discoverability of information about the function, helping to clarity the specifics of what can (or must) be passed to it. For functions you plan to distribute or reuse, please consider providing these details.