Forum Discussion

JClishe's avatar
JClishe
Icon for Helper II rankHelper II
8 years ago
Solved

How to make a formula result null when the source is null?

My subject is probably confusing but I didn't know how to phrase it. I have a dataset that contains a temperature column, in celsius. I've created a new column with a formula that converts the tem...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    In Power Query you can test on null values, like:

     

    let
    Source = #table(type table[Celsius = Int64.Type],{{0}, {null}, {37}}),
    #"Added Custom" = Table.AddColumn(Source, "Fahrenheit", each if [Celsius] = null then null else 1.8 * [Celsius] + 32)
in
    #"Added Custom"

     

    But I guess you are looking for a DAX solution. This works with me:

     

    Fahrenheiit = if(ISNUMBER([Celsius]), [Celsius] * 1.8 + 32,BLANK())
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
8 years ago

In Power Query you can test on null values, like:

 

let
    Source = #table(type table[Celsius = Int64.Type],{{0}, {null}, {37}}),
    #"Added Custom" = Table.AddColumn(Source, "Fahrenheit", each if [Celsius] = null then null else 1.8 * [Celsius] + 32)
in
    #"Added Custom"

 

But I guess you are looking for a DAX solution. This works with me:

 

Fahrenheiit = if(ISNUMBER([Celsius]), [Celsius] * 1.8 + 32,BLANK())
JClishe's avatar
JClishe
Icon for Helper II rankHelper II
8 years ago

Awesome that worked! Thanks so much.