Forum Discussion
Need help with formula - column with multiple date formats
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"
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]
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"It works! Thank you!