Date Field Issue

[Anonymous]

Hi, 

One of my data contains a text field as below. Request help to convert it to date column by removing letters in Query editor

Date

2021-06-15T00:06:00Z

Requirement 

DATE

6/15/2021 12:06:00 AM

Thanks in advance

Suren

[v-rzhou-msft · Accepted solution]

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@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.

[Anonymous]

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