Need help with formula - column with multiple date formats

[ddrury426]

I am in the process of cleaning a dataset and found that I can't convert one of my columns to a date since it has multiple date formats in the results (sample column below).  I'm trying to create a custom column to convert all to a short date, but I'm having trouble.  What would be the best way to accomplish this?

DATE

44266.334028

3/11/2021 8:01

null

44308.750694

43858.473611

1/28/2020 11:22

[ronrsnfld]

Try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYvBDcAgDMRWqfKuIJeEkLIKYgPUX/dvCz/LsnsnM3FPqsYSNM5OmoEsLDiiMZa6nzkXmClHqoX9si00SiSr6tjpt8Z/8wE0ERrjBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t]),
    #"to Date" = Table.TransformColumns(Source, {"DATE", each try Date.From(Number.From(_)) otherwise DateTime.Date(DateTime.From(_,"en-US")), type nullable date})
in
    #"to Date"

[AlienSx]

Hi, @ddrury426 

    Table.TransformColumns(
        Source,
        {"DATE",
        each if _ = null then _ else Date.From(try Number.Round(Number.From(_), 0) otherwise DateTime.From(_))}
    )

[ddrury426]

That doesn't seem to help.  The column ends up coming in as a text field that I can't change the data type on.  I'd like to keep the null values as null, and when I enter the formula, I end up with an error: 

Expression.Error: We cannot convert a value of type Function to type Type.
Details:
Value=[Function]
Type=[Type]

[AlienSx · Accepted solution]

it's not a formula. It's a step in your query. Here is working example of the code with a table of one column DATE as a Source. I also added final step to make type date. Try to add 2 final steps into your query and replace Source reference with your previous step name. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYvBDcAgDMRWqfKuIJeEkLIKYgPUX/dvCz/LsnsnM3FPqsYSNM5OmoEsLDiiMZa6nzkXmClHqoX9si00SiSr6tjpt8Z/8wE0ERrjBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t]),
    transformation = Table.TransformColumns(
        Source,
        {"DATE",
        each if _ = null then _ else Date.From(try Number.Round(Number.From(_), 0) otherwise DateTime.From(_))}
    ),
    #"Changed Type" = Table.TransformColumnTypes(transformation,{{"DATE", type date}})
in
    #"Changed Type"

[ddrury426]

It works!  Thank you!