Handling null when adding columns from table with no results

[KNP]

Hi,

That's a somewhat confusing subject line. 

I'll try and keep this short as I suspect there's a very easy solution for someone with more Power Query knowledge than me.

I'm adding a column to a table using a custom function.

In some cases (depending on the parameters passed) there is no result returned by this function.

This produces the new column (as intended) but all values are null (not the type that can be replaced).

I would like to know the most efficient/elegant way to handle this scenario and return 0 instead of null.

Thanks,

Kim

[Nolock · Accepted solution]

Hi @KNP,

you have 2 options: you integrate a test for null into you function or you do that after calling it. The later looks like:

= Table.AddColumn(
     LastStep, 
     "NewColumnName", 
     each
          let
               functionResult = myFunction(_, another param, another param),
          in
               if functionResult = null then 0 else functionResult
    ) 

The important thing is that you call your custom function only once and save the result into a variable.

A proof: The first query runs for 5 seconds, the other for 10 seconds.

/*
let
    Source = Function.InvokeAfter(() => 5, #duration(0, 0, 0, 5)),
    Result = if Source = null then 0 else Source
in
    Result
*/

let
    Result = if Function.InvokeAfter(() => 5, #duration(0, 0, 0, 5)) = null then 0 else Function.InvokeAfter(() => 5, #duration(0, 0, 0, 5))
in
    Result