Forum Discussion
change a data/time column in GMT format to data/time/timezone format in local time
I am importing a dataset in power BI from a database where I have a column as shown below. I want to create some time series graphs using this column as the x-axis. When I publish reports, I want the graph to have data and time values in the local time that a user is opening the report in? Any ideas on how to do this?
datetime
| 2018-06-01T19:30:02.000Z |
| null |
| null |
| null |
| 2018-05-31T21:59:19.000Z |
| 2018-06-01T16:30:00.000Z |
| 2018-06-01T14:16:04.000Z |
| 2018-05-18T22:03:41.000Z |
| null |
| 2018-06-01T18:57:33.000Z |
| 2018-06-01T17:48:04.000Z |
| null |
| 2018-06-01T16:10:10.000Z |
| 2018-05-31T19:34:01.000Z |
| 2018-05-18T13:34:32.000Z |
| 2018-06-01T19:22:06.000Z |
| 2018-05-28T21:04:02.000Z |
| null |
HI Art,
You can refer to below steps to create columns to switch datetime.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZAxDsMwDAP/krkuKMl2bL7DU438IOjW/zduhqaNAmgQQPBEsfdJISUgB0iTSgOhdwCPabn16fla14tl96Vg0lSYKqV+fUdo/kDhi5GbjvgvpiClqRLGKE6cI6IwzTTz+TNj+eG7iEzBNucUtpcSCXEjig3R1D9eOV7IZ6eW0Rmi0/XyBg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UTC = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"UTC", type datetimezone}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "LocalTimeZone", each if [UTC]<> null then DateTimeZone.SwitchZone([UTC], Number.From(DateTimeZone.ZoneHours(DateTimeZone.LocalNow()))) else null), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Datetime", each DateTime.From([UTC])), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"UTC", type text}, {"LocalTimeZone", type text}, {"Datetime", type datetime}}) in #"Changed Type1"Notice: power bi data model not support datetimezone format, so you need to switch them as text to stored with original format.
Regards,
Xiaoxin Sheng
3 Replies
HI Art,
You can refer to below steps to create columns to switch datetime.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZAxDsMwDAP/krkuKMl2bL7DU438IOjW/zduhqaNAmgQQPBEsfdJISUgB0iTSgOhdwCPabn16fla14tl96Vg0lSYKqV+fUdo/kDhi5GbjvgvpiClqRLGKE6cI6IwzTTz+TNj+eG7iEzBNucUtpcSCXEjig3R1D9eOV7IZ6eW0Rmi0/XyBg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UTC = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"UTC", type datetimezone}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "LocalTimeZone", each if [UTC]<> null then DateTimeZone.SwitchZone([UTC], Number.From(DateTimeZone.ZoneHours(DateTimeZone.LocalNow()))) else null), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Datetime", each DateTime.From([UTC])), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"UTC", type text}, {"LocalTimeZone", type text}, {"Datetime", type datetime}}) in #"Changed Type1"Notice: power bi data model not support datetimezone format, so you need to switch them as text to stored with original format.
Regards,
Xiaoxin Sheng
Thanks Xiaoxin, Do I type this into the advanced editor of the power BI to create new columns? Replace the exiting in statement with your statement and add let statements?
HI Art,
These formula is m query formula, you can enter to query editor to add 'add column' part to your query.
Regards,
Xiaoxin Sheng
