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
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: