{"id":2855,"date":"2024-06-05T22:27:11","date_gmt":"2024-06-06T03:27:11","guid":{"rendered":"http:\/\/ninmonkeys.com\/blog\/?p=2855"},"modified":"2024-06-05T22:31:10","modified_gmt":"2024-06-06T03:31:10","slug":"power-query-functions-part1-using-optional-parameters","status":"publish","type":"post","link":"https:\/\/ninmonkeys.com\/blog\/2024\/06\/05\/power-query-functions-part1-using-optional-parameters\/","title":{"rendered":"Power Query Functions ( Part 1): Introduction to Optional Parameters"},"content":{"rendered":"\n\n\n

You can view the final .pq query here <\/a><\/p>\n\n\n\n

3 Main Methods to Declare Optional Parameters<\/h2>\n\n\n\n

There’s 3 main ways you can declare optional parameters in your own functions.<\/p>\n\n\n\n

  1. By setting your parameter type to nullable type.

    name as text<\/code> becomes
    name as nullable text<\/code>

    This version requires you to always pass something, even if it’s null.

    <\/li>
  2. By setting the type to optional like

    optional name as nullable text<\/code> . You can shorten it by skipping the nullable part
    optional name as text<\/code> (     Because optional parameters are implicitly nullable<\/a> )


    Unlike #1, you can skip optional parameters when calling functions.

    <\/li>
  3. By using a record<\/code> parameter.

    Usually it’s a optional options as record<\/code>

    This lets you call a function with any combination of keyword arguments.

    <\/li>
  4. Merging Default Values With the User’s Record<\/li><\/ol>\n\n\n\n

    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<\/strong>. I’ll cover this in a future post.<\/p>\n\n\n\n

    Creating Your Own Custom Functions <\/h2>\n\n\n\n

    Let’s write a wrapper that calls Text.Combine<\/a> . It’s a simple example to see how the parameter types differ<\/p>\n\n\n\n

        Join_Nullable = (texts as list, separator as nullable text) =>\n        Text.Combine( texts, separator ),\n\n    Join_Optional = (texts as list, optional separator as text) =>\n        Text.Combine( texts, separator ),<\/code><\/pre>\n\n\n\n
    Type 1: Using Nullable Parameters<\/h3>\n\n\n\n
    Regular nullable parameters are still mandatory. Even if it’s null. <\/p>\n\n\n\n
    \n
    \n
    \/\/ input [1]\nJoin_Nullable( chars, ", " )\n\n\/\/ input [2]\nJoin_Nullable( chars, null )\n\n\/\/ input [3]\nJoin_Nullable( chars )<\/code><\/pre>\n\n\n\n
    <\/p>\n<\/div>\n\n\n\n
    \n
    \/\/ output [1]\na, b, c, d, e, f, g, h\n\n\/\/ output [2]\nabcdefgh\n\n\/\/ output [3]\nError: 1 arguments were passed\nto a function which expects 2.<\/code><\/pre>\n\n\n\n
    <\/p>\n<\/div>\n<\/div>\n\n\n\n
    \"\"<\/figure><\/div>\n\n\n\n
    Type 2: Optional Parameters<\/h3>\n\n\n\n
    If functions end with optional parameters, you can skip them entirely.<\/p>\n\n\n\n
    \n
    \n
    \/\/ input [1]\nJoin_Optional( chars, ", " )\n\n\/\/ input [2]dfsdf\nJoin_Optional( chars, null )\n\n\/\/ input [3]\nJoin_Optional( chars )<\/code><\/pre>\n<\/div>\n\n\n\n
    \n
    \/\/ output [1]\na, b, c, d, e, f, g, h\n\n\/\/ output [2]\nabcdefghfds\n\n\/\/ output [3]\nabcdefghfds<\/code><\/pre>\n\n\n\n
    <\/p>\n<\/div>\n<\/div>\n\n\n\n
    Type 3 Examples: Using Optional Records<\/h2>\n\n\n\n
    Text.JoinString = (strings as list, optional options as record) as text => let\n        Prefix    = options[Prefix]?    ?? "",\n        Suffix    = options[Suffix]?    ?? "",\n        Delimiter = options[Delimiter]? ?? ","\n    in\n        Prefix & Text.Combine( strings, Delimiter ) & Suffix<\/code><\/pre>\n\n\n\n
    You can mix and match any combinations of the keys Prefix<\/strong>, Suffix<\/strong>, and Delimiter<\/strong> .<\/p>\n\n\n\n
    names = {"Jen", "Hubert", "Nobody", "Somebody" },\n\nDefault       = Text.JoinString( names ) ,\nAsCsv         = Text.JoinString( names, [ Delimiter = ", "] ),\n\nAsTablePipes  = Text.JoinString( names, [\n    Prefix = "| ", Delimiter = " | ", Suffix = " |" ] ),\n\nAsBullets     = Text.JoinString( names, [\n    Prefix    = " #(2022) ",\n    Delimiter = " #(cr,lf) #(2022) "\n])<\/code><\/pre>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    Examples In The Standard Library<\/h2>\n\n\n\n
    Type 2 Examples: Optional Parameters<\/h3>\n\n\n\n
    When you import columns with Table.TransformColumnTypes<\/a>, the UI doesn’t include the culture parameter. Unless you choose “using locale”. <\/p>\n\n\n\n
    \/\/ it's declared as\nTable.TransformColumnTypes( \n     table as table, typeTransformations as list,\n     optional culture as text) as table\n\n\/\/ The GUI skips the culture parameter \n= Table.TransformColumnTypes( Source,{  {"Date", type text}} )\n\n\/\/ if you click on "locale"\n= Table.TransformColumnTypes( Source,{  {"Date", type text}}, "en-GB" )<\/code><\/pre>\n\n\n\n
    Type 3 Examples: Using Optional Records<\/h3>\n\n\n\n
    \/\/ It's declared as\nDateTime.FromText( text as nullable text, optional options as any) as nullable datetime\n\n\/\/ without the optional parameter\n= DateTime.FromText("2010-12-31T01:30:25")\n\n\/\/ They pass 2 keyword-style arguments.\n= DateTime.FromText(\n     "30 Dez 2010 02:04:50.369730", \n     [ \n          Format = "dd MMM yyyy HH:mm:ss.ffffff", \n          Culture="de-DE" \n     ] )<\/code><\/pre>\n\n\n\n
    Why does DateTime.FromText<\/a> use options as any<\/strong> and not options as record<\/strong> ? 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<\/code> parameter.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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. By setting your parameter type to nullable type. name as text becomesname as nullable text This version requires you to always pass something, even if it’s null. […]<\/p>\n","protected":false},"author":1,"featured_media":2945,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[150,13,9],"tags":[160,162,159,161,6,7],"_links":{"self":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/2855"}],"collection":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/comments?post=2855"}],"version-history":[{"count":91,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/2855\/revisions"}],"predecessor-version":[{"id":2950,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/2855\/revisions\/2950"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media\/2945"}],"wp:attachment":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media?parent=2855"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/categories?post=2855"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/tags?post=2855"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}