Forum Discussion
Custom Query/Function to convert UTC to EST
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 DoneEDIT: 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.
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
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
Thank you for posting! I found this very helpful in solving a similar issue.
Anonymous thank you so much! this did exactly what i was looking for. Just have one question, will it auto adjust to DTS?
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.
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
