Power query function optional arguments

Question

How can I create a power query function with an optional argument pls? I have tried various permutations of creating the function syntax, presently like this:

let
    fnDateToFileNameString=(inFileName as text, inDate as date, optional inDateFormat as nullable text) => 
    let

        nullCheckedDateFormat = Text.Replace(inDateFormat, null, ""),
        value =     if nullCheckedDateFormat = "" 
            then inFileName
            else Text.Replace(inFileName, inDateFormat, Date.ToText(inDate, inDateFormat ))
    in 
        value
in
    fnDateToFileNameString

and I'm passing it to a test that looks like:

 = fnDateToFileNameString("XXXXXXXXXXXXXXXXXX", #date(2015, 3, 21), null)

That throws:

"An error occurred in the fnDateToFileNameString" query. Expression.Error: we cannot convert the value null to type Text.
    Details:
    Value=
    Type=Type

score 11 · Accepted Answer · edited Apr 04 '16 at 03:27

The issue is in Text.Replace, since the second argument cannot be null: it doesn't make sense to replace the character null in a text value. Your function will work if you change nullCheckedDateFormat to the following: nullCheckedDateFormat = if inDateFormat = null then "" else inDateFormat,

This is a bit redundant with the next step, so you could rewrite the function like this:

let
    fnDateToFileNameString=(inFileName as text, inDate as date, optional inDateFormat as nullable text) => 
    if inDateFormat = null or inDateFormat = ""
    then inFileName
    else Text.Replace(inFileName, inDateFormat, Date.ToText(inDate, inDateFormat ))
in
    fnDateToFileNameString

Power query function optional arguments

1 Answers1