Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Daven
Helper II
Helper II

Converting UTC to EST

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

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

v-kelly-msft
Community Support
Community Support

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!

 

View solution in original post

5 REPLIES 5
Akinais
New Member

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
Community Support
Community Support

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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.