Excel Power Query Replace error in one column with values from another column

[afzalbaharudin]

Hi wonderful community,

I have an Excel table that I loaded to Power Query and I can't figure out how to replace Error values with the value in another column (See snapshot).

Does anyone know how to replace the "Error" in [Day] column with its respective value from [Earl.start] by using Power Query?

I've attached the Excel file if you want to play around with it.

Many thanks and have a good day!

[#2]

@afzalbaharudin you can click on advanced editor and paste this code.  

Row 4 of the code tries to evaluate the value from "Day" as a date and if it fails, it uses the value from "Earl.start" instead.   

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type any}, {"Earl.start", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "NewDay", each try Date.From([Day]) otherwise Date.From([Earl.start])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Day", type date}, {"Earl.start", type date}, {"NewDay", type date}})
in
    #"Changed Type1"

Obviously this provides an additional column so you can delete the original and rename the new to Day (and reorder if it's important).  

[#3 · Accepted solution]

@afzalbaharudin 

As variant

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {{"Day", type date}, {"Earl.start", type date}}),
    #"Replaced Value" = Table.ReplaceValue(
        Table.ReplaceErrorValues(#"Changed Type", {{"Day", null }}),
        null,
        each _[Earl.start],
        Replacer.ReplaceValue,{"Day"}),
    DayToDate = Table.TransformColumnTypes(
        #"Replaced Value",{{"Day", type date}})
in
    DayToDate