Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |