change a data/time column in GMT format to data/time/timezone format in local time

[Art]

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

[v-shex-msft · Accepted solution]

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

[Art]

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?

[v-shex-msft]

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