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.
Hello,
I have a table with timestamped data of datatype datetimezone, eg. 07-08-2020 08:00:00 + 02:00.
How do I convert this to datetime in a way that takes tz info into account, eg. 07-08-2020 10:00:00?
Note that the + 02:00 part has been added to the timestamp, not simply stripped away.
If I try to manually convert the datatype, I get an error.
Cheers, Mike
Hi @h4tt3n ,
You can acheive this by splitting the column.Below i have attached the screenshots for your understanding.
Split column:
Select or enter delimeter : Custom
Use +
Split at: Left-most delimeter
Click on OK
Result :
If your problem is solved please accept this as solution.
Thank you
orry, this simply discards timezone info, it doesn't incorporate it into the time. However, I have found the solution with a python script.
Hi, Can you share the steps you used to add the timestamp to the date using python
@Macc-PA I think this is the script. After all, it's one year ago, and we ended up not using it anyway.
Thanks for that - in the end we didnt need to use it.
The problem was solved by ensuring our odata datasource was sending the datetime as UTC, ie not converting it to local time.
Once that happened, converting the column to DataTime applied the UTC offset as expected even for datetimes where daylight saving was involved.
In effect you are asking to convert a datetimezone value to a datetime in UTC:
let
dtz = ...,
udtz = DateTimeZone.SwitchZone(dtz, 0),
dt = DateTimeZone.RemoveZone(udtz)
in
dt
Hi @h4tt3n
Convert into datetime but add another step
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNbDQNTIwMlAwsLAyMAAiBW0DIyClFBsLAA==", 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 datetimezone}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1", type datetime}})
in
#"Changed Type1"
I have tried to add the conversion as another step, but unfortunately this doesn't work either. Challenge remains unsolved.
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 |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |