Replace null values with contents from another column

[abehrmann]

I am attempting to replace the null values with the values from completion note date in the far left. 

If possible i would like to use the exitsting completion date column instead of creating a new one. 

Any suggestions 

 

[Anonymous · Accepted solution]

If a measure is sutible for your needs then a simple measure liek the following would work. Replace max with whatever aggregation you need

measure =
VAR _Column1 =
    MAX ( Column1 )
RETURN
    IF ( ISBLANK ( _Column1 ), MAX ( Column2 ), _Column1 )

If a measure is not sutible then the easiest way is to add a new column either in the model or in the Query Editor.

The DAX version would be 

Column3 =
IF ( ISBLANK ( Column1 ), Column2, Column1 )

The Power Query Version would be

if [Column1] = null then [Column2] else [Column1]

Then remove the old column. With Power Query you can do it in one step without adding a new column but it is not possible with the UI you need to use the advanced editor. I have included an example below, the key step is 

Table.ReplaceValue(#"Changed Type",null,each [Column1],Replacer.ReplaceValue,{"Column2"})

let
    Source = Table.FromRows({{1,null},{2,2},{3,3},{null,4},{5,null}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,each [Column1],Replacer.ReplaceValue,{"Column2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Column2", Int64.Type}})
in
    #"Changed Type1"