How to replace null values in datetime values

Hi Anonymous ，

Approve with amitchandak , when there is null value in a data column, it won’t get error.

It seems that you used the null value in somewhere else?

Please try to replace these null values with 9999/12/31

Here is the M code:

let

    Source = Excel.Workbook(File.Contents("C:\xxx\New Microsoft Excel Worksheet.xlsx"), null, true),

    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type date}, {"Column2", type number}}),

    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,#date(9999, 12, 31),Replacer.ReplaceValue,{"Column1"})

in

    #"Replaced Value"

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.