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 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!

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

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    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