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 temperature to fahrenheit. My formula is as follows:

 

Fahrenheit = ([Celsius] * 1.8) + 32 

 

However - not all of my source celsius data rows contain temperature data, it's missing on many of them. But on the new fahrenheit column I've created the temperature shows as 32 on the rows where the celsius data was missing. If the celsius data is missing, than I'd like the fahrenheit data to be blank, not 32. 

 

Ultimately this is what I want to have happen:

  • If celsius source data is null, fahrenheit data should be null
  • If celsius source data is 0, fahrenheit data should be 32

 

Any thoughts how I can accomplish this?

 

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())

2 Replies

  • 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())