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

 

However this does not work in the report after publishing to the PowerBI service, which seems to use UTC as its local time.

If my data source reads 18:00 UTC, ToLocal has no effect on the data -- it is still shown as 18:00 in the PowerBI Service.

 

A simple solution would just be to subtract X hours from my UTC times in PowerQuery. The data would "look" like EST in PowerBI Desktop, and will "look" like EST in the PowerBI service (even though PowerBI thinks it is a UTC time).

 

However this does not account for daylight savings at all, which isn't trivial, because DST starts and ends on a different day each year.

 

So I'd like an M function that can...

 

-> Read in a DateTime that is in UTC

-> Check if that DateTime falls into Daylight Savings

--> If yes, subtract 5 hours

--> If no, subtract 4 hours

 

Any ideas?? Surely this has been solved somewhere!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Sure -- here is the custom function I wrote, with the help of posters above. 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])

     

    Thanks again all for the help.

     

    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

     

4 Replies

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi Anonymous ,

     

    If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!

     

    Best Regards,
    Yingjie Li

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Sure -- here is the custom function I wrote, with the help of posters above. 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])

       

      Thanks again all for the help.

       

      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