SOLVED

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

New Contributor

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

 

afzalbaharudin_0-1661896795527.png

 

 

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 Replies

@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).  

best response confirmed by afzalbaharudin (New Contributor)
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