Custom Query/Function to convert UTC to EST

[Anonymous]

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 (or some other creative approach) 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
• Be easily applied to multiple columns

Any ideas?

[Anonymous]

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 (or some other creative approach) 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
• Be easily applied to multiple columns

Any ideas?

[edhans]

Glad it helped @Anonymous 

[edhans · Accepted solution]

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]

@edhans This is helpful, but I'm not sure it's 100% what I need.

This query relies on the current date to determine if we're in DST or not, no? The solution needs to check the date which is being converted.

Imagine I have three years worth of data all in UTC, and need to convert them all to EST. Thoughts?

The solution will have to be some custom function that takes the date as an input, rather than relying on varToday. I'm just not well versed enough in M to write it!

[edhans]

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

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

becomes

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

In otherwords you would not use varToday. Just add a custom column that might ahve this full formula

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

Make sense? If not, send back some data via links below and a screen shot from Excel of expected output.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

---

@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

[Jruiz218]

@Anonymous thank you so much! this did exactly what i was looking for. Just have one question, will it auto adjust to DTS?

[edhans]

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]

@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]

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. 

[Anonymous]

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