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?

davidebacci · Accepted Answer · 2022-12-12 07:49:02Z

3

Change your function definition to be the following:

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

answered Dec 12, 2022 at 7:49

davidebacci

31.4k4 gold badges20 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Muppet Bear Over a year ago

See Optional Parameters section bengribaudo.com/blog/2017/11/28/4199/…

Muppet Bear · Accepted Answer · 2022-12-12 20:35:31Z

0

PQ has a null-coalesce operator ??. Try this: Inputs = {input1 ?? "", input2 ?? ""}

edit from the future - #2 is wrong. My bad. Still, read Ben's guide. It's the best PQ text-book there is.

Once you fix the fn, each... Combine([col3],[col4]..) will break because you forgot to _ before [col3] and [col4]. each..._ is syntactic sugar for a single-argument function (eating a whole row, in this case). See here: https://bengribaudo.com/blog/2017/12/08/4270/power-query-m-primer-part3-functions-function-values-passing-returning-defining-inline-recursion

edited Dec 12, 2022 at 20:35

answered Dec 12, 2022 at 6:38

Muppet Bear

1529 bronze badges

2 Comments

Rasec Malkic Over a year ago

Thanks for answer. I´ve added what you suggested Inputs = {input1 ?? "", input2 ?? ""} but still I get the error. I didn't understand what to do for what you mentioned in item 2.

Muppet Bear Over a year ago

Yup, my bad on the _[ ]. PQ will try and access the column value from the record that is passed to it in a general namespace. So _[col3] = [col3]

Dmitrij Holkin · Accepted Answer · 2022-12-14 11:15:05Z

0

Use

each try Text.Combine() otherwise ""

edited Dec 14, 2022 at 11:15

answered Dec 12, 2022 at 8:35

Dmitrij Holkin

2,0755 gold badges41 silver badges90 bronze badges

2 Comments

Rasec Malkic Over a year ago

I tried you suggestion but I got error like TRY and OTHERWISE are not understood

Muppet Bear Over a year ago

That's because try and otherwise need to be lowercase

Collectives™ on Stack Overflow

How to handle null values in custom function?

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related