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 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 |
Solved! Go to 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
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
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 |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |