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

[bhmiller89]

@v-caliao-msft  I don't have a UTC column, could you describe how to invoke that? 

[WRudd]

Hello,

I watched @HowtoPowerBI Video (https://youtu.be/xOj7KNqe_cI?si=OsOxVOV7LZRtm3p-) which helped me so much! The only thing I had to plug in a number until I got the time to match the central time I needed. See image to see the number in red box I changed which is different from the video. I hoep this helps. 

[MarcelBeug]

If you have a local datetimezone, then you can convert it to UTC using DateTimeZone.ToUtc:

let
    Source = #table(type table[LocalDateTimeZone = datetimezone],{{#datetimezone(2017,12,6,11,30,15,-4,-30)}}),
    AddedUTC = Table.AddColumn(Source, "UTC", each DateTimeZone.ToUtc([LocalDateTimeZone]))
in
    AddedUTC

[ppfisterer]

I have followed this process successfully at the query level.  However, when referencing the ClientTime in a report, the time zone information is stripped out and the fields revert to Date/Time from the Date/Time/Timezone format; times revert to UTC.

How can this data be used in a report?  Is this a known issue with Power BI?

[pftcg]

Having a similar issue, when I'm in the Query Editor and convert the column type to "Date/Time/Timezone" it shows the proper -05:00. However on the actual report as a Date/Time it shows UTC.

[Betsy]

Hmm. I didn't have that issue, but I ran the client time as a one off report, so I deleted the UTC time column after I converted a client time replacement. I wonder if that makes a difference?

[Betsy]

Hey @ppfisterer,

Is your report in the service? I had no issues making the report using client time on the desktop, with the caveat that I created a time reference table with the hours listed, that I then related the client time transformation back to, That may help with the time conversion issue (I use both time and date reference tables to tie my various tables together. I found time, in the general sense, to not work very well in BI without them).

Betsy

[ppfisterer]

Betsy,

yes, I have both time and date reference tables.   I duplicated the UTC column in the souce table twice. In one column apply Date and in the second apply Time.  The values there respect the time zone.  If I then reference these new columns in a table in the report, they no longer reflect the adjusted time zone but show the UTC time again.  The original field with Date/Time/Timezone format does look ok .  Here are the steps from the query editor

= Table.DuplicateColumn(#"Added Custom2", "RoomTimeStamp", "RoomTimeStamp - Copy")

= Table.RenameColumns(#"Duplicated Column",{{"RoomTimeStamp - Copy", "RoomStartDate"}})

= Table.TransformColumnTypes(#"Renamed Columns",{{"RoomStartDate", type date}})

= Table.DuplicateColumn(#"Changed Type", "RoomTimeStamp", "RoomTimeStamp - Copy")

= Table.RenameColumns(#"Duplicated Column1",{{"RoomTimeStamp - Copy", "RoomStartTime"}})

= Table.TransformColumnTypes(#"Renamed Columns2",{{"RoomStartTime", type time}})

UTC  = 11/19/2016 4:25:43 AM +00:00

for E. Australia Standard Time 10      

RoomTimeStamp = 11/19/2016 2:25:43 PM +10:00 

RoomStartDate = 11/18/2016   ??

RoomStartTime = 11:25:43 PM   ??

The RoomTimeStamp is correct but when the values have types applied as Date or Time, the data doesn't make sense.

-Phil

[ppfisterer]

Instead of applying a different Type to the column, a Transform to extract the date and time seems to do the trick.

[sahilhira162]

Hi @v-caliao-msft
This solution is perfect, but I am struggling with Daylight saving time. My data is in UTC and I want to convert it into EDT and EST repsectively. Therefore from Mar - Nov I want EDT data i.e. UTC - 4:00, whereas from Nov - Mar I want EST date i.e. UTC - 5:00. Is there any wany I can get it fixed in Power BI.
Thanks in advance. Any help is appreciated.

Regards,
Sahil

[avikkal89]

Hi @sahilhira162 - I have a similar business need to be implemented. Have you been able to figure this issue? 

[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

[Anonymous]

@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