Forum Discussion

kfschaefer's avatar
kfschaefer
Helper IV
10 years ago
Solved

Calculate column handling Null values

Need to add 2 columns together, however one of the columns may contain a Null value and if Null the return the original value.

 

what is the proper syntax?  I need to add the Revenue columns and also the Cost columns together and if one is null the return the column that contains an actual value.

 

 

  • Sean's avatar
    Sean
    10 years ago

    kfschaefer

    select the column => Transform tab => Replace Values => Value To Find: null Replace With: 0

     

    You may have do this step "Replaced Value" before the "Added Custom" where you add the 2 columns

8 Replies

  • Sean's avatar
    Sean
    Community Champion
    10 years ago

    kfschaefer

    select the column => Transform tab => Replace Values => Value To Find: null Replace With: 0

     

    You may have do this step "Replaced Value" before the "Added Custom" where you add the 2 columns

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Wherever possible I avoid changing data.

      Try this instead:

      each List.Sum({[ColA], - [ColB], [ColC]})

       

      to perform ColA - ColB + ColC

      My full line being:

      = Table.AddColumn(#"Previous Step Name", "Sum", each List.Sum({[ColA], - [ColB], [ColC]}), Int64.Type)
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Thank you Lolo,

        I will give it a try!

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hello,

      I have tried this and it worked for the sum column, thank you.

      However, I also need to add a custom column that concatenates those same number type columns (plus others that are text type), so for that I need to replace nulls with "" rather than "0", otherwise the custom column captures the "0" as a text, so it doesn't 'ignore' it. I have tried to sort this out (the type mix type problem) by using Text.From, but this function doesn't accept null values, since it returns null as long as one column contains null in that row... unless one of you guys come up with an easy solution I couldn't figure out I think I will up and turn to Excel (after the query) or DAX when I load to the model. Do you know where I could find a guide or in which language use for each case and in which order should the transformations be applied?

      Many thanks in advance

      Javier

       

       

  • MattAllington's avatar
    MattAllington
    Community Champion
    10 years ago

    try this (case sensitive)

     

    =if [column 1] = null then [column 2] else [column 1] + [column 2]

  • Rickmaurinus's avatar
    Rickmaurinus
    Helper V
    6 years ago

    There's plenty of ways to do it. I like the List.Sum option. But one could also catch the Nulls through conditional-if statements. Or one could also replace null by 0.  Take one that feels comfortable and if the performance is bad, try another. Also check out: 

     

    3 Ways to SUM null values in Power Query 

     

    Cheers,

    Rick

     

    --------------------------------------------------

    @ me in replies or I'll lose your thread

     

    Master Power Query M? -> https://powerquery.how

    Read in-depth articles? -> BI Gorilla

    Youtube Channel: BI Gorilla

     

    If this post helps, then please consider accepting it as the solution to help other members find it more quickly.