How to handle null values in custom function?

Question

I'm trying to add a custom column to combine values of 2 columns (Col3 and Col4) with the result of a custom function fnMyFunction() in this way

#"Added Custom" = Table.AddColumn(#"Previous Step", "Custom Column", 
     each 
      Text.Combine( 
        {
            [Col3],
            [Col4],
            fnMyFunction([Col5],[Col6])
         }
        )),

I'm getting this error when function handles null values

Expression.Error: We cannot convert the value null to type Text.
Details:
    Value=
    Type=[Type]

The function fnMyFunction is like this:

(input1 as text, input2 as text)=>
let
    Inputs = {input1, input2},
    SplitAndZip = List.Zip(List.Transform(Inputs, each Text.ToList(_))),
    OtherStep
    ...
    ..
    LastStep
in
    LastStep

I've tried to add the if else in Input step in order to get empty as output for the function but doesn't work

(input1 as text, input2 as text)=>
let
    Inputs = if input1 <> null then {input1, input2} else {"",""}, //Added "if else" here
    SplitAndZip = List.Zip(List.Transform(Inputs, each Text.ToList(_))),
    OtherSteps
    ...
    ..
    LastStep
in
    LastStep
    

How can be fix this?

Answer 1

Change your function definition to be the following:

(optional input1 as text, optional input2 as text)=>