Forum Discussion

KNP's avatar
KNP
Super User
7 years ago
Solved

Handling null when adding columns from table with no results

Hi,

 

That's a somewhat confusing subject line. :smileyindifferent:

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

 

Need Help
  • Nolock's avatar
    Nolock
    7 years ago

    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

1 Reply

  • Nolock's avatar
    Nolock
    Resident Rockstar
    7 years ago

    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