Return value of a column if another column is null/blank?

[slatka11]

Hi there,

I have the below 3 columns all in the same table (call it TABLE_1) and I am looking to return the value of one column if the other is blank:

Month	Projected Rate	Actual Rate
December	1.35	1.34
January	1.35
February	1.35

What I am looking to acheive is that if the value in the "Actual Rate" column is blank, return the value in the "Projected Rate" column.

Thanks for your help.

[slatka11]

Thank you very much

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula

=if(isblank(Data[Actual rate]),Data[projected rate],Data[Actual rate])

See if this shorther calculated column formula works as well

=coalesce(Data[Actual rate],Data[projected rate])

Hope this helps.

[kjbox76]

What if I have two columns that contain null values and I want the priority to be if either of the two columns have data return that value and if not, return 'NO KEY'

Example:

[Ashish_Mathur]

Hi,

I would do this in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUVKK1YlWAtKmZuYWYDZUHMKPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"US PRODUCT KEY" = _t, #"CAN PRODUCT KEY" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"US PRODUCT KEY", Int64.Type}, {"CAN PRODUCT KEY", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [US PRODUCT KEY]=null and [CAN PRODUCT KEY]=null then "No Key" else List.FirstN(List.Select(Record.ToList(_), each _<>null),1)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Hope this helps.

[umeshk1255]

Hi, 

What if I have to do same thing with both null and 0. I have both in my columns. 

Thank you, 

Umesh

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.