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