Convert UTC to client time zones?

[Betsy]

Hi,

I have date/time information imported from csv files in UTC format. I also have imported time zone information for my various clients. What I'd like to do is create a series of calculated columns where date/time is reported in client time. So I could look at responses on Monday mornings vs. Friday mornings, for example. 

How would I do this?

Thanks for the help!

Betsy

[MarcelBeug]

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

[NatashaSchuster]

@MarcelBeug

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

[MarcelBeug]

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).

[stevemenke]

Hi, this post was very helpful, but I am having a problem using it with a MySql data source. Whenever I apply the changes or refresh the data I get an message about an error on every row. However, the error list is empty when I try to view them. Also the data seems to calculate as expected. I tried the same approach with a CSV data source and I don't get any errors. Is there a work around for using it with MySql or a different approach?

-Steve

[stevemenke]

Hello, I am trying to do the same function but from data from a MySql Database. The strange thing is that I get an error for every row in the table when I apply or refresh, but the data actually converts as expected. Then when view the errors the list is empty?

-Steve

[Betsy]

@v-caliao-msft 

Thanks, these are fantasic directions! How do I get to the first step [UTC] which stores the changed hour count? My data looks like this:

Time                                 Student Timezone

10/13/2015 3:10:23PM       Australia/Sydney

Thanks a million!

Betsy