Forum Discussion
Convert UTC to client time zones?
Hi sahilhira162 - I have a similar business need to be implemented. Have you been able to figure this issue?
Currently, I'm working on a workaround to convert date/times between Windows time zones.
So far, my playlist has 2 videos, including converting date/times of the events of the Olympic Winter Games from Korean Standard Time to any Windows time zone of your choice, and how to create a table with clock switches for a Windows time zone.
The workaround is based on a table with all UTC Offsets for all Windows time zones in the period 2015-2024.
If I take pieces of the solution and apply to this topic, then this query - ClockSwitchesEasternTime - returns a table with clock switches for Eastern Time (I pasted this part via option "Enter Data"):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZQ9i8JAEIb/ypJKQdnZ2Xx4diJXBg7USi0CprDQ4rz7/85m5+JZOIvMQhrD4/vyMOTd74vJbrueQwUwNZ/d7af/vprt+dKbyW5jDr8AWJt1d+1O3bSYFc46i+Aq43AJQI9ZtfSa/h9/BgStH6DSNMx8tcVx9maTt4vY1DwXlWPRSPgH8XaNG43ql0IPaUWRt86HlFoS+kOURnVMkYwYURphSGlEI0aURlVMkYwYURoNl16IRowojcqYIhkxojSCkPIhGjGiNPIxRTJiRGcUvnqE1C4QkWEXKCW1C4QoLxTujE68ECNKoyamSEaMZFg6xOTSEZJh6SgltXSEZFg69MmlIyTD0lFKaukIybALWCZ3gZAMu0ApqV0g5L/R8Q4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TimeZone = _t, UTC = _t, #"Offset After" = _t, #"Local After" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"TimeZone", type text}, {"UTC", type datetime}, {"Offset After", type duration}, {"Local After", type datetime}})
in
#"Changed Type"And this query - UTC to Eastern Time - will convert date/times to Eastern Time (as Source, I pasted some random date/times via option "Enter Data"):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XVFJbsQgEPwK8jlSb9CYvuUBkeZuzf+/kcIsTkbiAkVt3dd1GEklZanJQznE0+vneH9dx0kqHfDUQjSKLkCEBCCrgiIWxgvJU6ulGuZhZ/oegFIZ7xDK8l9L89BqwQB1UeowAUdDcXgBTj0W7qV7SFlSSq3/P9MZVqOU9V+YZg8PFpgsAiK14YCoJbItRiPpcTUnQVZQdkGEmpTWKdq2ySh4JkM52Kz3MutZb8FR2i6uo4agBhh5jx3mk4FZSf0Ty8h3quyR86I42ZiIo3ZwewhwuQEJxUbkaS5TCjIAnnU85lE4dC/wFhp54S7eXV4fUL4TI9sel205xCp9jl3u/Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"UTC DateTime" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UTC DateTime", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Eastern Date Time", (ThisRow) => ThisRow[UTC DateTime] + List.Last(Table.SelectRows(ClockSwitchesEasternTime, each [UTC] <= ThisRow[UTC DateTime])[Offset After]), type datetime)
in
#"Added Custom"
ClockSwitchesEasternTimeUTC to Eastern Time
- Anonymous8 years agoNot applicable
Another great post. I see you are using [Offset After] . How exact is this feature used?
= Table.AddColumn( ChangedTypeToDateTime, "Eastern Date Time", (ThisRow) => ThisRow[UTC DateTime] + List.Last( Table.SelectRows( ClockSwitchesEasternTime, each [UTC] <= ThisRow[UTC DateTime])[Offset After] ), type datetime- MarcelBeug8 years ago
Community Champion
The part
Table.SelectRows(ClockSwitchesEasternTime, each [UTC] <= ThisRow[UTC DateTime])
returns a table.
By adding [Offset After], the "Offset After" column of that table is returned as a list of values (i.c. durations).