CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktopdata-cleaningm
Rasec Malkic
Rasec Malkic

Reputation: 659

How to handle null values in custom function?

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?

Upvotes: 1

Views: 994

Answers (3)

Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

Use

each try Text.Combine() otherwise ""

Upvotes: 0

Muppet Bear
Muppet Bear

Reputation: 152

  1. 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.

  1. 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

Upvotes: 0

davidebacci
davidebacci

Reputation: 30174

Change your function definition to be the following:

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

Upvotes: 3

Related Questions