Forum Discussion
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 c...
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
Super User
Super Userit'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"
It works! Thank you!