cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Betsy
Helper IV
Helper IV

Convert UTC to client time zones?

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

1 ACCEPTED SOLUTION
v-caliao-msft
Microsoft
Microsoft

Hi @Betsy,

 

We may take a try with the method below:

Assuming currently we have the Time and UTC column in a table, where [Time] stores the actual UTC time, [UTC] stores the changed hour count between client and UTC.
Based on the different Time Zone, UTC values changes within -12 to 12 range.

111.png
 

When data loaded into Power BI, open Query Editor, under the Add column tab, click Add Custom Column, then under the formula part, copy and paste the following:
DateTime.AddZone([Time],0)
222.png

 

Click OK. This step adds the default UTC Time Zone (0 here) under the time stored in [Time], to the newly created ZoneTime column;
After that, we need to create another column to switch the time zone to the client, also click on the Add custom column, then copy and paste the following formula:
DateTimeZone.SwitchZone([ZoneTime],[UTC])
3333.png
 

After that, we have successfully added the time Zone information into the time. Then we need format the column into date/time/timezone format.
Select ClientTime column, navigate to Transform tab, choose data type and select Date/Time/TimeZone:
444.png
 

After that, click close and apply.
Till then we should have finished converting the time into Client local time.


If any further questions, please feel free to post back.

 

Regards,

Charlie Liao

View solution in original post

20 REPLIES 20
Jay7
Frequent Visitor

I had to display last refreshed time in the report. When I published from my local machine, it had Eastern time zone. However, when it was schedule refreshed in Power BI Server, it displayed UTC time.

 

I modified current time logic as below:

 

DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),10))

 

1. Get UTC time

2. Switch zone to Eastern zone (+10:00)

3. Remove zone from the date time(If I dont do this, it displays UTC time with +10:00. When I changed it's type to DateTime or text, it did not work)

 

You can use "DateTimeZone.From" if you already have the date and time. I haven't tested this though. 

Anonymous
Not applicable

You stated:

""You can use "DateTimeZone.From" if you already have the date and time. I haven't tested this though.""

 

This worked for me, I had the [UTC DateTime] field and I was able to use DateTimeZone.From to convert the UTC (as text) to a new (local) [DateTime] (as DateTimeZone) field.

 

BUT - if your UTC comes with a TZ (time zone offset), this function does NOT take that into account. It just parses the DateTime and does not use the offset to adjust the result to the local time...

 

Which I am working on figuring out how to do.

 

 

 

v-caliao-msft
Microsoft
Microsoft

Hi @Betsy,

 

We may take a try with the method below:

Assuming currently we have the Time and UTC column in a table, where [Time] stores the actual UTC time, [UTC] stores the changed hour count between client and UTC.
Based on the different Time Zone, UTC values changes within -12 to 12 range.

111.png
 

When data loaded into Power BI, open Query Editor, under the Add column tab, click Add Custom Column, then under the formula part, copy and paste the following:
DateTime.AddZone([Time],0)
222.png

 

Click OK. This step adds the default UTC Time Zone (0 here) under the time stored in [Time], to the newly created ZoneTime column;
After that, we need to create another column to switch the time zone to the client, also click on the Add custom column, then copy and paste the following formula:
DateTimeZone.SwitchZone([ZoneTime],[UTC])
3333.png
 

After that, we have successfully added the time Zone information into the time. Then we need format the column into date/time/timezone format.
Select ClientTime column, navigate to Transform tab, choose data type and select Date/Time/TimeZone:
444.png
 

After that, click close and apply.
Till then we should have finished converting the time into Client local time.


If any further questions, please feel free to post back.

 

Regards,

Charlie Liao

View solution in original post

Anonymous
Not applicable

@v-caliao-msft / Charlie Liao

Is it planned to provide an implementation by Microsoft for

Power BI service to evaluate the browser time zone settings?

 

Best would be:

  1. Get the current time zone setting of the connected browser
  2. Allow (via dedicated datetime methods) filtering and display to use that request specific user time zone

= Users from Australia see the same reports as those from Europa as those from the USA.
But: Those from Australia with the appropriate Australian time zone, those from Europe ...

 

Example:

The very same record would show 01:40 May 1, 2018 in Australia
But: 17:40 April 30, 2108 in Switzerland

And: 11:40 April 30, 2018 in New York

 

Regards, David

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

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

 

Specializing in Power Query Formula Language (M)

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
Frequent Visitor

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.

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?

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
Frequent Visitor

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

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

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

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"

 

 

 

ClockSwitchesEasternTimeClockSwitchesEasternTimeUTC to Eastern TimeUTC to Eastern Time

Specializing in Power Query Formula Language (M)

@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

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

Specializing in Power Query Formula Language (M)

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

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

@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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors