Forum Discussion

ddrury426's avatar
ddrury426
Regular Visitor
3 years ago
Solved

Need help with formula - column with multiple date formats

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

 

Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    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"

     

5 Replies

  • ddrury426's avatar
    ddrury426
    Regular Visitor
    3 years ago

    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's avatar
      AlienSx
      Super User
      3 years ago

      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"

       

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hi, ddrury426 

        Table.TransformColumns(
        Source,
        {"DATE",
        each if _ = null then _ else Date.From(try Number.Round(Number.From(_), 0) otherwise DateTime.From(_))}
    )
  • ronrsnfld's avatar
    ronrsnfld
    Super User
    3 years ago

    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"