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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago
edhans wrote:

Just change the function to pull your field, vs the current time.

 

 

 

 

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

becomes

= DateTimeZone.SwitchZone([YourUTCDateTimeZoneField],-7)

 

 

 

Hmm I think there is some disconnect here. I can't (and shouldn't) reference a column from a different in this query.

 

I have many fields/columns which need to be converted, not just one.

 

So the solution must take a datetime as an input, determine is that specific date falls within DST, then adjust that datetime appropriately. That way I can apply the solution to any number of columns.

 

In any case, your posts were helpful, I ultimately used them to write a custom function below. Very easy to adapt to any time zone. Could even take time zone as a parameter but I only need EST. It is implemented by simply adding a custom column in PowerQuery with function =UTCtoEST([DateColumn])

 

let
    UTCtoEST = (UTC_DateTime) =>
    let 
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc5BCsAgDETRu7gWdCZttWeR3P8amk6gux8eCVmrWAMbO0apBWi32usniGlKLnVKj+mVmFoyz8AugDpX4gAhGeqUOEBKHnVKvEb7XzvtvgE=", 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(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

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you for posting!  I found this very helpful in solving a similar issue.