How to Convert Datetime String with Zulu Suffix to Actual Datetime in Power BI?

[willayd]

I am sourcing data from a web service that provides datetime strings in the format of "2020-11-02T09:00:00Z" . I am looking for a built in way to convert these strings to timestamps in Power BI. TIMEVALUE is the closest DAX function I could find, but it's not clear from the documentation if TIMEVALUE is for datetime objects or just time objects. It also doesn't seem to work, yielding "Cannot convert value '2020-12-17T00:00:00Z' of type Text to type Date."

Is there a suggested way to robustly handle conversion of these strings to datetime objects?

[Fowmy · Accepted solution]

@willayd 

You can change the Data Type to Date/Time in Power Query and it will do the conversion. Paste the below code in na blank query and check please:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1NNI1NA8xMLACoyil2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}})
in
    #"Changed Type"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Syndicate_Admin]

Hello, I have not managed to change that format, I have a case; where the recorded data has an hour for example; 22:00 hours (10:00 pm) Colombia time on December 7. But in the table it is shown with 5 hours of more ie 3:00 hours of the next day that is to say 3:00 am time of Colombia but of December 8. It really has been a problem. If someone knows the solution better, I thank you for the help.

[willayd]

Thanks @Fowmy I ended up doing 

#"Changed Type" = Table.TransformColumnTypes(Tasks1,{{"Date", type datetimezone}) 

To retain timezone. Your post was very helpful in figuring this out. Not sure if DAX can handle but this seems to work back in the modeling layer