How do I replace nulls in a datetime column by filling with the value from another datetime column

[E_K_]

I need a column that replaces all its null values with the value from another datetime column that has no null values

I've tried replacing the nulls with 01/01/1800 and then wrote a conditional column to swap those values with the value from the other datetime column, however this has not worked out for my final visuals as 

I also have tried a distinct list of dates per X and dates per Y and then appended them however this has not worked either

[E_K_]

Is there a way to do this in power query? I am unable to make sliders for dates dynamic on graphs with a calculated column

[E_K_]

This did not work as a calculated column. Would this work trying in power query?

[danextian · Accepted solution]

Hi @E_K_ ,

If you want to do it in Power Query, you can follow either a replace by another column value approach or by creating a calculated column.

Use Table.ReplaceValue where in null in the first date column is to be replaced by the value in the other date column. Note: the data type will become type any after this so change the data type back to date.

= Table.ReplaceValue(#"Previoius Step",null,each [Date2],Replacer.ReplaceValue,{"Date1"})

Create a conditional column

= Table.AddColumn(#"Previoius Step", "Conditional Column", each if [Date1] = null then [Date2] else [Date1], type date)

Here's a sample M-Script for your reference. Create a blank query and replace the content with it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7BCcAwDAN38TtQyW3SdhaT/ddo8ik4ChgMh3QowsBjnMPdilkvYePDfzgJzjWDS0jdeVr23NJ6hLwrITZmMpnp0pLNlM3TU7OnpUz/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Date1] = null then [Date2] else [Date1], type date),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",null,each [Date2],Replacer.ReplaceValue,{"Date1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Date1", type date}})
in
    #"Changed Type1"

[E_K_]

Your second suggestion worked! Thanks so much - you've been a great help

[truptis]

Hi @E_K_ ,

Try this:

Result = IF(isblank([column1]), [column2], [column1])

here column1 is the column with the null values and column2 is the one with which you wish to refresh. 
@E_K_  If this helps you then please hit the thumbs up and mark it as a solution. Thanks.