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