Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @Daven ,
Check the reference below:
https://intellitect.com/convert-utc-local-time-daylight-savings-support-power-bi/
Code:
DateTimeZone.SwitchZone( <datetime>,DateTimeZone.ZoneHours(DateTimeZone.FixedLocalNow())))
Explanation:
DateTimeZone.ZoneHours(DateTimeZone.FixedLocalNow()) defines the offset from GMT
SwitchZones takes the offset for conversion
Hi @Daven ,
Check the reference below:
https://intellitect.com/convert-utc-local-time-daylight-savings-support-power-bi/
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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!
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |