Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Custom Query/Function to convert UTC to EST

I am pulling a  number of datetime columns from several sources, which are mostly in UTC. I'm using ToLocal to display these in EST, which is my local time, and is how my end users need to see the ...
Need Help
  • edhans's avatar
    edhans
    6 years ago

    To get the local timezone, use the following function, which I created in a blank query.

     

     

    = DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-7)

     

     

    Change the -7 to your normal non-DST offset.

     

    I also called that query varToday

     

    Now create another query like this:

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LczBDcAwCATBXvy2BIcT2anFov82ErL+7WkEe7dhCgvXbL1JdtPZf1GthVz0Ea/1IING1jfCAdHnpB6EkElnvg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [dtDSTStart = _t, dtDSTEnd = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"dtDSTStart", type date}, {"dtDSTEnd", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each (DateTime.Date(varToday) >= [dtDSTStart] and DateTime.Date(varToday) <= [dtDSTEnd])),
    #"Counted Rows" = Table.RowCount(#"Filtered Rows")
in
    #"Counted Rows"

     

     

    That will return a 1 or 0. 1 if we are in DST, 0 if not. I called this query varDST Note it references varToday above.

     

    Now all other functions should be using the same logic as the DateTimeZone.SwitchZone([UTC],-7) but you add varDST, which will either add 1 or 0.

     

    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done

     

    EDIT: This will not property work in the wee hours of the morning on DST switch dates. It assumes the entire day is or is not DST. YOu'd need to greatly expand the table to handle the 2am-3am switch.

edhans's avatar
edhans
Community Champion
5 years ago

No Jruiz218 - You have to do the DST changes yourself. I have an article here that shows you how to add a Refresh timestamp to your report and it discusses how you can adjust for DST with a table that has the start/end dates of DST for a few years. Use the correct dates for your region. These dates are for the US.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Jruiz218  yes this function adjusts for DST. Not sure why edhans  is saying no.

 

As an aside, I found the performance of this function to be very poor -- if you are converting a lot of dates, I suggest you create a DST table in PowerQuery, then reference that table in the function -- 

 

 

let
    UTCtoEST = (UTC_DateTime) =>
    let 
        Source = Table.Buffer(Table_DST),
        #"Filtered Rows" = Table.SelectRows(Source, each (DateTime.Date(UTC_DateTime) >= [dtDSTStart] and DateTime.Date(UTC_DateTime) <= [dtDSTEnd])),
        result = DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTime.AddZone(UTC_DateTime,0),-5 + Table.RowCount(#"Filtered Rows")))
    in
        result
in
    UTCtoEST

 

 

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    Sorry Anonymous - you are making some adjustment to DST, but there is not one DST calendar. See this article for 2021. Many countries have no DST, and many others have different calendars, and still others have multiple DST calendars (or lack thereof) in different areas. Arizona in the US as one example doesn't have DST.

     

    That is why I told Jruiz218 they would need to look at the article I provided and adjust for DST according to the timezone(s) as applicable.

     

    But yes, if you and Jruiz218 are on the same DST calendar, then it works.