Forum Discussion
Date Field Issue
Hi Anonymous
If you change value like 2021-06-15T00:06:00Z to Date/time format directly, it may show wrong datetime.
In my test, it will show 2021/06/15 08:06:00 AM in my test, this will be impacted by timezone.
If all date values are in type of xxxx/xx/xx T...Z. You can replace T by space and replace Z by null. Then change your data type as datetime.
Result is as below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MNM1NA0xMLAyMLMyMIhSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"T"," ",Replacer.ReplaceText,{"Date"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Z","",Replacer.ReplaceText,{"Date"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Date", type datetime}}) in #"Changed Type"Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous I was able to change that to a datetime:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MNM1NA0xMLAyMLMyMIhSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}})
in
#"Changed Type"If you need it as a date, just add a step to change the type to date after changing it to datetime.
Thank you Greg for your suggestion. But being a begginer i am unable to reproduce same at my end. Please let me know how it can be done with a "Custom Column"
Suren