Converting UTC to EST

[Daven]

Hi, 

I am looking to convert UTC datetime column to EST daylight saving. How do I convert it in PowerBI?

Thank for your help in advance

Daven

[edhans · Accepted solution]

To just convert what @mahoneypat posted works, but if you are trying to compensate for DST dynamically, see the following M code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZDLCQAhDAV78Sxonv9axP7bWLNPMN4mDMYhc7oUBAFRmvNOJBTy8r8RnTpNJh8TdRo0iUzT94BIIeTzRBdAaBr5GF0A0FTyMZqGdNM2mwDkG7CZZuhQKEA2ZdWI+pQ1U9ae/7v5v9vTYNzTYNiyFG/Z5rU+", 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}}),
    varCurrentDate = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8)),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [dtDSTStart] < varCurrentDate and [dtDSTEnd] > varCurrentDate),
    varDSTOffset = Table.RowCount(#"Filtered Rows"),
    #"Last Refresh Date" = #table(
        type table
            [
                #"RefreshDate"=datetimezone
            ],
        {
            {DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8 + varDSTOffset,0)}
        }
        )
in
    #"Last Refresh Date"

If, in Power Query, you select the gear icon next to the Source, you will see the table for US DST on/off times. YOu need to change my -8 for PST to -5 for EST. This will dynamically add or not add 1 hr for DST as needed depending on the system calendar.

If you just want to calculate the DST offset (1 or 0), then remove the last lenghty step:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZDLCQAhDAV78Sxonv9axP7bWLNPMN4mDMYhc7oUBAFRmvNOJBTy8r8RnTpNJh8TdRo0iUzT94BIIeTzRBdAaBr5GF0A0FTyMZqGdNM2mwDkG7CZZuhQKEA2ZdWI+pQ1U9ae/7v5v9vTYNzTYNiyFG/Z5rU+", 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}}),
    varCurrentDate = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8)),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [dtDSTStart] < varCurrentDate and [dtDSTEnd] > varCurrentDate),
    varDSTOffset = Table.RowCount(#"Filtered Rows")
in
    varDSTOffset

Then, using the formulas @mahoneypat listed, you can use something like 

=DateTimeZone.SwitchZone([UTC Date Column],-8 + varDSTOffset,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
5) See this article if you need help using this M code in your model.

[Akinais]

Code:

DateTimeZone.SwitchZone( <datetime>,DateTimeZone.ZoneHours(DateTimeZone.FixedLocalNow())))

Explanation:

DateTimeZone.ZoneHours(DateTimeZone.FixedLocalNow()) defines the offset from GMT

SwitchZones takes the offset for conversion

[v-kelly-msft · Accepted solution]

Hi @Daven ，

Check the reference below:

https://intellitect.com/convert-utc-local-time-daylight-savings-support-power-bi/

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[edhans · Accepted solution]

To just convert what @mahoneypat posted works, but if you are trying to compensate for DST dynamically, see the following M code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZDLCQAhDAV78Sxonv9axP7bWLNPMN4mDMYhc7oUBAFRmvNOJBTy8r8RnTpNJh8TdRo0iUzT94BIIeTzRBdAaBr5GF0A0FTyMZqGdNM2mwDkG7CZZuhQKEA2ZdWI+pQ1U9ae/7v5v9vTYNzTYNiyFG/Z5rU+", 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}}),
    varCurrentDate = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8)),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [dtDSTStart] < varCurrentDate and [dtDSTEnd] > varCurrentDate),
    varDSTOffset = Table.RowCount(#"Filtered Rows"),
    #"Last Refresh Date" = #table(
        type table
            [
                #"RefreshDate"=datetimezone
            ],
        {
            {DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8 + varDSTOffset,0)}
        }
        )
in
    #"Last Refresh Date"

If, in Power Query, you select the gear icon next to the Source, you will see the table for US DST on/off times. YOu need to change my -8 for PST to -5 for EST. This will dynamically add or not add 1 hr for DST as needed depending on the system calendar.

If you just want to calculate the DST offset (1 or 0), then remove the last lenghty step:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZDLCQAhDAV78Sxonv9axP7bWLNPMN4mDMYhc7oUBAFRmvNOJBTy8r8RnTpNJh8TdRo0iUzT94BIIeTzRBdAaBr5GF0A0FTyMZqGdNM2mwDkG7CZZuhQKEA2ZdWI+pQ1U9ae/7v5v9vTYNzTYNiyFG/Z5rU+", 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}}),
    varCurrentDate = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8)),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [dtDSTStart] < varCurrentDate and [dtDSTEnd] > varCurrentDate),
    varDSTOffset = Table.RowCount(#"Filtered Rows")
in
    varDSTOffset

Then, using the formulas @mahoneypat listed, you can use something like 

=DateTimeZone.SwitchZone([UTC Date Column],-8 + varDSTOffset,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
5) See this article if you need help using this M code in your model.

[vchauhan011]

Hi @edhans,

I am facing the similar issue where I need to convert UTC datetime column to EST daylight saving. Your solution is very usefull however I am stuck at the second step. I created a new query and put the M code you provided. Now I am not sure where to put the below formula. Do I create a new step in the Query where I have my datetime column in UTC timezone? I tried doing so, it gives me error that varDSTOffset wasn't recognized.
Sorry, this might a stupid question but I am not sure what to do now.   

=DateTimeZone.SwitchZone([UTC Date Column],-8 + varDSTOffset,0)

Thank you very much in advance! 

[mahoneypat]

In the query editor, you can use one of the DateTimeZone functions like DateTimeZone.ToLocal or DateTimeZone.SwitchZone.

https://docs.microsoft.com/en-us/powerquery-m/datetimezone-zonehours

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat