Power Query Functions ( Part 1): Introduction to Optional Parameters

You can view the final .pq query here

3 Main Methods to Declare Optional Parameters

There’s 3 main ways you can declare optional parameters in your own functions.

1. By setting your parameter type to nullable type.
   
   name as text becomes
   name as nullable text
   
   This version requires you to always pass something, even if it’s null.
   
   
2. By setting the type to optional like
   
   optional name as nullable text . You can shorten it by skipping the nullable part
   optional name as text ( Because optional parameters are implicitly nullable )
   
   
   Unlike #1, you can skip optional parameters when calling functions.
   
   
3. By using a record parameter.
   
   Usually it’s a optional options as record
   
   This lets you call a function with any combination of keyword arguments.
   
   
4. Merging Default Values With the User’s Record

With record parameters you can merge the user’s record with your your own default values.
It lets the user pick and choose which defaults to override — compared to using nulls as an “all-or-nothing” approach. I’ll cover this in a future post.

Creating Your Own Custom Functions

Let’s write a wrapper that calls Text.Combine . It’s a simple example to see how the parameter types differ

    Join_Nullable = (texts as list, separator as nullable text) =>
        Text.Combine( texts, separator ),

    Join_Optional = (texts as list, optional separator as text) =>
        Text.Combine( texts, separator ),

Type 1: Using Nullable Parameters

Regular nullable parameters are still mandatory. Even if it’s null.

// input [1]
Join_Nullable( chars, ", " )

// input [2]
Join_Nullable( chars, null )

// input [3]
Join_Nullable( chars )

// output [1]
a, b, c, d, e, f, g, h

// output [2]
abcdefgh

// output [3]
Error: 1 arguments were passed
to a function which expects 2.

Type 2: Optional Parameters

If functions end with optional parameters, you can skip them entirely.

// input [1]
Join_Optional( chars, ", " )

// input [2]dfsdf
Join_Optional( chars, null )

// input [3]
Join_Optional( chars )

// output [1]
a, b, c, d, e, f, g, h

// output [2]
abcdefghfds

// output [3]
abcdefghfds

Type 3 Examples: Using Optional Records

Text.JoinString = (strings as list, optional options as record) as text => let
        Prefix    = options[Prefix]?    ?? "",
        Suffix    = options[Suffix]?    ?? "",
        Delimiter = options[Delimiter]? ?? ","
    in
        Prefix & Text.Combine( strings, Delimiter ) & Suffix

You can mix and match any combinations of the keys Prefix, Suffix, and Delimiter .

names = {"Jen", "Hubert", "Nobody", "Somebody" },

Default       = Text.JoinString( names ) ,
AsCsv         = Text.JoinString( names, [ Delimiter = ", "] ),

AsTablePipes  = Text.JoinString( names, [
    Prefix = "| ", Delimiter = " | ", Suffix = " |" ] ),

AsBullets     = Text.JoinString( names, [
    Prefix    = " #(2022) ",
    Delimiter = " #(cr,lf) #(2022) "
])

Examples In The Standard Library

Type 2 Examples: Optional Parameters

When you import columns with Table.TransformColumnTypes, the UI doesn’t include the culture parameter. Unless you choose “using locale”.

// it's declared as
Table.TransformColumnTypes( 
     table as table, typeTransformations as list,
     optional culture as text) as table

// The GUI skips the culture parameter 
= Table.TransformColumnTypes( Source,{  {"Date", type text}} )

// if you click on "locale"
= Table.TransformColumnTypes( Source,{  {"Date", type text}}, "en-GB" )

Type 3 Examples: Using Optional Records

// It's declared as
DateTime.FromText( text as nullable text, optional options as any) as nullable datetime

// without the optional parameter
= DateTime.FromText("2010-12-31T01:30:25")

// They pass 2 keyword-style arguments.
= DateTime.FromText(
     "30 Dez 2010 02:04:50.369730", 
     [ 
          Format = "dd MMM yyyy HH:mm:ss.ffffff", 
          Culture="de-DE" 
     ] )

Why does DateTime.FromText use options as any and not options as record ? In this case it’s for backwards compatibility. If they were writing it from scratch today, it could have used a record. Older versions used a type text parameter.