CODEWITHSUNDEEP
excelpowerquery
gregn
gregn

Reputation: 1330

Power query function optional arguments

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

Upvotes: 9

Views: 13217

Answers (1)

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4134

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

Upvotes: 11

Related Questions