Converting null values into default date in power Query

[Dunner2020]

Hi there,

I have a script that converts the date column into local standard time and also considers the daylight saving. The date column (I want to convert) has also got null values. I used the script as a query and creating a new date column using that script. I tried to include the if statement to replace the null value with '1/1/1900' as the default value but it looks like it did not work. Here is my code:

= (datetimecolumn as datetime) =>

let

date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
lastSundayOfSeptember = Date.StartOfWeek(#date(Date.Year(date), 9, 28), Day.Sunday),
FirstSundayOfApril = Date.StartOfWeek(#date(Date.Year(date), 4, 7), Day.Sunday),

isSummerTime = (date = lastSundayOfSeptember and time >= #time(1,0,0))
or
(date > FirstSundayOfApril and date < lastSundayOfSeptember)
or
(date = FirstSundayOfApril and time >= #time(1,0,0)),

timeZone = (-13 - Number.From(isSummerTime))*-1,

MDT = if date <> null then
DateTime.From(date)
+ #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))
+ #duration(0, timeZone, 0, 0)
else
Date.FromText("1/1/1900")

in
MDT

After running the above code, it still returns the error for the null values. The error says that I could not convert the null value into the DateTime type. I have limited knowledge of power query. Could anyone help me where I made the mistake?

[AllisonKennedy · Accepted solution]

@Dunner2020  I'm not sure if t is d it be the as haven't dug into this yet, but could it be that you're using Date.FromText instead of DateTime.FromText? 

Alternatively, you could do a replace values for null to 1/1/1900 first, then run the custom function.