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.
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
Solved! Go to Solution.
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.
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)
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])
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:
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
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"
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).
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
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
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |