cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
suren947
Helper I
Helper I

Date Field Issue

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

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @suren947 

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.

1.png

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.

3.png

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.

View solution in original post

3 REPLIES 3
RicoZhou
Community Support
Community Support

Hi @suren947 

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.

1.png

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.

3.png

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.

View solution in original post

Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!