Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I'm not sure where is the better place to do this manipulation, but I have a column with the date/time as following:
The time is in UTC and I want to convert this time to Mountain Daylight Time:
-between second Sunday of March and first Sunday of November you go back 6 hrs so UTC - 6
-between first Sunday of November and second Sunday of March you go back 7 hrs so UTC - 7
I found this article online that shows how to account for daylight savings on the refresh date: https://powerpivotpro.com/2019/01/dst-refresh-date-function-power-bi-service/
But I need to transform a column of data to the appropiate time. Is this possible? If it is, is it better to do in Power Query or Desktop of Power BI? Any help would be much appreciated.
Thank you.
Solved! Go to Solution.
Hi @Anonymous ,
I think there are many ways, for example I tried to find a pattern in order to catch the November first Sunday or March second Sunday, and for your specific needs, maybe this custom function could work:
(datetimecolumn as datetime) => let date = DateTime.Date(datetimecolumn), time = DateTime.Time(datetimecolumn), firstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday), SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday), isSummerTime = (date = SecondSundayOfMarch and time >= #time(1,0,0)) or (date > SecondSundayOfMarch and date < firstSundayOfNovember) or (date = firstSundayOfNovember and time >= #time(1,0,0)), timeZone = (7 - Number.From(isSummerTime))*-1, MDT = DateTime.From(date) + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time)) + #duration(0, timeZone, 0, 0) in MDT
So for dates from March Second Sunday at 1:00am until November First Sunday at 12:59:59am you will get your datetime - 6 hours and for dates from November First Sunday 1:00am until March Second Sunday at 12:59:59am you will get your datetime - 7 hours
According to Saint Google, the time is changed after 1:00am if you need it to be changed after 12:00am instead just remove first and last condition from isSummerTime
If you have any question or if you find any error on the code, just let me know.
Regards,
Gian Carlo Poggi
Sure @Anonymous ,
Right click on Queries pane and add a new Blank Query:
Then right click on this new query and select Advanced Editor:
In this new window erase all, paste the my code and click DONE:
Now that query was converted into a function, you can rename it if you like, for example to "UTC_to_MDT":
Then in order to use this function in your table you have different options, one option is going to your query or table, then click on Add Column / Invoke Custom Function, then put a name to this new column, select your function (in my case UTC_to_MDT) and select the column from your table you need to apply this function to (in my case "Date"):
And then you will see the new date added :
Hope this helps.
Regards,
Gian Carlo Poggi
Thanks for sharing this solution, it's pretty disappointing that PowerQuery doesn't have proper time zone support.
I've tweaked it for conversion from UTC to UK time (aka Europe/London in tzdata)
From When do the clocks change? - GOV.UK (www.gov.uk):
In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, and back 1 hour at 2am on the last Sunday in October.
The period when the clocks are 1 hour ahead is called British Summer Time (BST). There’s more daylight in the evenings and less in the mornings (sometimes called Daylight Saving Time).
When the clocks go back, the UK is on Greenwich Mean Time (GMT).
datetime version:
(datetimecolumn as datetime) =>
let
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
// From https://www.gov.uk/when-do-the-clocks-change
// In the UK the clocks go forward 1 hour at 1am on the last Sunday in March,
// and back 1 hour at 2am on the last Sunday in October.
// Last Sunday in March
ForwardDate = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),
// Last Sunday in October
BackDate = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday),
isSummerTime =
(date = ForwardDate and time >= #time(1,0,0))
or
(date > ForwardDate and date < BackDate)
or
(date = BackDate and time < #time(1,0,0)),
timeZone = Number.From(isSummerTime),
Europe_London = datetimecolumn + #duration(0, timeZone, 0, 0)
in
Europe_London
datetimezone version:
let
Source = (datetimecolumn as datetimezone) =>
let
// This version ignores the zone information in the input, but adds it to the output
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
// From https://www.gov.uk/when-do-the-clocks-change
// In the UK the clocks go forward 1 hour at 1am on the last Sunday in March,
// and back 1 hour at 2am on the last Sunday in October.
// Last Sunday in March
ForwardDate = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),
// Last Sunday in October
BackDate = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday),
isSummerTime =
(date = ForwardDate and time >= #time(1,0,0))
or
(date > ForwardDate and date < BackDate)
or
(date = BackDate and time < #time(1,0,0)),
timeZone = Number.From(isSummerTime),
Europe_London = DateTime.AddZone(DateTimeZone.RemoveZone(datetimecolumn) + #duration(0, timeZone, 0, 0) , timeZone)
in
Europe_London
in
Source
I hope that's useful to someone!
Hi Syndicate_Admin,
You write "...and back 1 hour at 2am on the last Sunday in October", then there should be time < #time(2,0,0)) instead of time < #time(1,0,0)).
(I had a similar error, I fixed it in my "UTC to CET" post.)
No, because I'm converting from UTC to UK time. The "2 am" is in British Summer time, which is 01:00 UTC.
The #time(2,0,0) function is time zone independent. When the current time (UTC, CET...) reaches 2 am, it switches to winter time.
When the clocks go back, each minute looks like this in UTC and local time:
The government says "Clocks go back at 2am" because your clock is in BST right up until that moment, at which point it should become GMT.
The source data is in UTC, so you need to figure out what the switch time is in UTC, which will probably not be the same as the switch time in local time (might even be on a different day for places like Australia!)
I already got the hang of it, it's been a long time since I did it. I tried entering times into a function in PowerBI and that worked, but our application sends times to PowerBI in UTC, so you're right it is necessary to find out what the switch time is in UTC versus local time. 👍 Thank you.
I could't find a simple Dax for this so I came up with a pretty simple solution. This is UTS to Central with DLS.
Var SundayMarch should actual be...
Hey! I created this DAX formula to convert from UTC to PDT. Considering the following rules:
Daylight Saving: Pacific Daylight Time (PDT) is a daylight saving/summer timezone, however during winter some places switch clocks for one hour back and observe Pacific Standard Time (PST).
Start: Pacific Daylight Time (PDT) started on Sunday, March 14, 2021 at 2:00 am local time and clocks were set one hour forward to Sunday, March 14, 2021, 3:00 am. Daylight saving starts annually the on second Sunday of March
End: Pacific Daylight Time (PDT) ends on Sunday, November 7, 2021 at 2:00 am local time and clocks are set one hour back to Sunday, November 7, 2021, 1:00 am local standard time instead. Daylight saving ends annually the on first Sunday of November
UTC to PDT =
VAR CurrentDate = DATE(YEAR('Date'[Date]),MONTH('Date'[Date]),DAY('Date'[Date]))
var CurrentTime = TIME(HOUR('Date'[Date]),MINUTE('Date'[Date]),SECOND('Date'[Date]))
var March = DATE(2022,3,1)
var November = DATE(2022,11,1)
VAR SecondSundayMarch = FILTER(
ALL('Date'[Date]),
YEAR('Date'[Date]) = YEAR(CurrentDate) &&
MONTH('Date'[Date]) = MONTH(March) &&
DAY([Date]) > 7 &&
DAY([Date]) < 15 &&
WEEKDAY([Date],1) = 1)
VAR FirstSundayNov = FILTER(
ALL('Date'[Date]),
YEAR('Date'[Date]) = YEAR(CurrentDate) &&
MONTH('Date'[Date]) = MONTH(November) &&
DAY([Date]) >= 1 &&
DAY([Date]) < 8 &&
WEEKDAY([Date],1) = 1)
VAR IsSummerTime = OR(AND(CurrentDate = SecondSundayMarch, CurrentTime >= time(9,0,0)), OR(AND(CurrentDate > SecondSundayMarch, CurrentDate < FirstSundayNov), AND(CurrentDate = FirstSundayNov, CurrentTime <= time(8,59,0))))
VAR TimeDiff = TIME(8-IsSummerTime,0,0) // If is summer time diff = 7 else time diff = 8
RETURN CurrentDate-TimeDiff
@Anonymous Can you please explain in steps more how do you used this formula?
I have table with UTC timestamp. Is this formula needs to be created as measure in same table?
Thanks,
Neha
Hi @nehajadhav166
I've only used this in M in Power BI Services.
1. Create a custom function
(datetimecolumn as datetime) =>
let
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
lastSundayOfOctober = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday),
lastSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),
isSummerTime = (date = lastSundayOfMarch and time >= #time(2,0,0))
or
(date > lastSundayOfMarch and date < lastSundayOfOctober)
or
(date = lastSundayOfOctober and time <= #time(2,0,0)),
timeZone = 1 + Number.From(isSummerTime),
CET =
DateTime.From(date)
+ #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))
+ #duration(0, timeZone, 0, 0)
in
CET
2. Invoke custom function on the date column of your table to create a new column with the timestamp convertion
I'm not able to provide you with a more detailed description at this point, and my screenshots are in Norwegian, but I hope this at least will help you a bit along the way.
Hello, great solution! However, I was getting "We couldn't authenticate with the credentails provided. Please try again." error after I refreshed the query. My datasource is a SharePoint List and it showed that I was signed in but the credentails couldn't be autenticated. The error went away after I removed the invoked function columns. Any idea how to fix this?
Hi bogdans,
I will test this for you and inform you as soon as I get it . And you also could refer to this link for details.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
I think there are many ways, for example I tried to find a pattern in order to catch the November first Sunday or March second Sunday, and for your specific needs, maybe this custom function could work:
(datetimecolumn as datetime) => let date = DateTime.Date(datetimecolumn), time = DateTime.Time(datetimecolumn), firstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday), SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday), isSummerTime = (date = SecondSundayOfMarch and time >= #time(1,0,0)) or (date > SecondSundayOfMarch and date < firstSundayOfNovember) or (date = firstSundayOfNovember and time >= #time(1,0,0)), timeZone = (7 - Number.From(isSummerTime))*-1, MDT = DateTime.From(date) + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time)) + #duration(0, timeZone, 0, 0) in MDT
So for dates from March Second Sunday at 1:00am until November First Sunday at 12:59:59am you will get your datetime - 6 hours and for dates from November First Sunday 1:00am until March Second Sunday at 12:59:59am you will get your datetime - 7 hours
According to Saint Google, the time is changed after 1:00am if you need it to be changed after 12:00am instead just remove first and last condition from isSummerTime
If you have any question or if you find any error on the code, just let me know.
Regards,
Gian Carlo Poggi
Hi @gpoggi
Could you pls explain the logic here? I just used 5 instead of 7 for EST but trying to understand how this works.
Appreciate your help!
Tried this but date column looks like below with "-5:00" and getting error in the new column - thoughts on what might be wrong?
When I try to use above code in my table I get error:
Expression.Error: The DateTime.Date function expects an input of type DateTime or DateTimeZone.
Details:
[Table]
Column is of datetimezone. I also tried with datetime type but error is same for both data type.
Can you please guide me where I am going wrong?
Thanks,
Neha
You need your column to be formatted to Date-time. If you try to use date-time-timezone it will fail. When you invoke the function the datetimecolumn in the bottom field must be formatted to datetime only.
@tmarton ,
datetime column is of datatype datetime but still have same error.
What could be area of issue?
here is my m query:
let
Source = Table.SelectColumns(ProductData,{"Assembly_TimeStamp_USPacific","Assembly_Message_Payload_SourceTimestamp_USPacific"}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Assembly_Message_Payload_SourceTimestamp_USPacific", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "AssemblyTimeStamp", each if [Assembly_TimeStamp_USPacific] = null then [Assembly_Message_Payload_SourceTimestamp_USPacific] else [Assembly_TimeStamp_USPacific]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"AssemblyTimeStamp", type date}, {"Assembly_TimeStamp_USPacific", type date}, {"Assembly_Message_Payload_SourceTimestamp_USPacific", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each HourMinTable1),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Time"}, {"Custom.Time"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom.Time", type time}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"AssemblyTimeStamp", type text}, {"Custom.Time", type text}}, "en-US"),{"AssemblyTimeStamp", "Custom.Time"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateTime"),
#"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns",{{"DateTime", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"DateTime", "Assembly_DateTime"}}),
date = DateTime.Date("Assembly_DateTime")
in
date
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
33 | |
16 | |
12 | |
11 | |
9 |
User | Count |
---|---|
44 | |
24 | |
16 | |
14 | |
14 |