Forum Discussion

Art's avatar
Art
Frequent Visitor
8 years ago
Solved

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...
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
Art
Frequent Visitor
8 years ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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