Convert UTC to client time zones?

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. 

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. 

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,

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

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

Hi, I will try this approach. I understand what u try to do here.
my question is, is this PQ in a dataflow or power bi desktop?