Forum Discussion

afzalbaharudin's avatar
afzalbaharudin
Copper Contributor
Aug 30, 2022
Solved

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

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...
BI & Data Analysis
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 31, 2022

    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
DexterG_III's avatar
DexterG_III
Iron Contributor
Aug 30, 2022

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

Resources