Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
Does this happen when you select the column to invoke the function? Also the line below says timestmap but you have it as date, is that correct?
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"AssemblyTimeStamp", type date}, {"Assembly_TimeStamp_USPacific", type date},
When I try to extract date with below line in advanced editor it gives error:
date = DateTime.Date(datetimecolumn)
Column I am using is Assembly_DateTime which is datetime and not Assembly_TimeStamp_USPacific(this is of type date).
Ok so you have your datetime column and I am guessing you want to also have just a date column that comes from Assembly_DateTime. You need to duplicate the column before you extract the date. If that is not the case, if you could provide a screen shot of your columns so I can see what you have that could help.
for replenishment and minor correction '>=' to '<' for proper operation on the day of change from summer time to winter time.
UTC(GMT) to CET
= (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
For summer time dates from March last Sunday at 2:00am until October last Sunday at 2:59:59am you will get your datetime +2 hours and for winter time dates from October last Sunday 3:00am until March last Sunday at 1:59:59am you will get your datetime +1 hour.
Good spot for catching this, but a minor correction - it should be "<" not "<="
Thanks for your comment, you are right. I edited my post.
Hi Jan_3
This is really great! I'm just not quite sure how to implement this in Power BI. I'll paste my query so for below. Could you give me a quick pointer on how to integrate this into the query? The datetimecolumn is called "created".
let
Source = *****,
*****,
#"Andre kolonner fjernet" = Table.SelectColumns(eplehuset_order_events, {"id", "fk_order_id", "fk_user_id", "created", "type", "data", "data_id"}),
#"Filtrere ut kun statusendringer" = Table.SelectRows(#"Andre kolonner fjernet", each [type] = 20),
#"Filter etter 01.11.19" = Table.SelectRows(#"Filtrere ut kun statusendringer", each [created] >= #datetime(2019, 11, 1, 0, 0, 0)),
#"Fjerne tomme" = Table.SelectRows(#"Filter etter 01.11.19", each [fk_order_id] <> null and [fk_order_id] <> "" and [created] <> null and [created] <> ""),
#"BRIDGE Statusendringer inkrementell-63726561746564-autogenerated_for_incremental_refresh" = Table.SelectRows(#"Fjerne tomme", each DateTime.From([created]) >= RangeStart and DateTime.From([created]) < RangeEnd)
in
#"BRIDGE Statusendringer inkrementell-63726561746564-autogenerated_for_incremental_refresh"
Thanks in advance.
Aleks
Hi Aleks
It is best to creat a new function:
right click on Queries -> New Query -> Other Sources -> Blank Query -> right click on this new query and select Advanced Editor -> insert code
= (datetimecolumn as datetime) =>
let ...
Then apply to the desired column: Add Column -> Invoke a custom function (maybe different in EN version)
good luck
This is a wonderful solution and works perfectly!
I have an add-on question to this. I have multiple time zones to deal with and have the Time Zone stored in the data table (e.g. Amercias/Vancouver). How would I go about updating this function to calculate the appropriate offset based on the time zone for each row?
Any guidance would be most appreciated.
Chris
Hi Chris,
I see no one replied to you, and if the data is on SQL Server 2016 or greater, it might be best to just let SQL Server do the time conversion to whatever TimeZone you are in, rather than building out for each one in Power BI. I am unsure whether other database types have the same ability or not.
You can see the info about the SQL Server conversion, and my revised Power BI code at https://community.powerbi.com/t5/Power-Query/Convert-Date-Time-in-UTC-to-Local-Time-with-Daylight-sa....
In my post at https://community.powerbi.com/t5/Power-Query/Convert-Date-Time-in-UTC-to-Local-Time-with-Daylight-sa... I mentioned a good video that explains the SQL Server ability to convert the timezone. If your database already knows what timezone it is in, you probably only need to do add the following to adjust it to EST, or change the value in quotes to whatever the TimeZone you want them all to change into.
AT TIME ZONE 'US Eastern Standard Time' AS EST
Hope this is helpful.
Jeff
Hi.
This is a great solution but I'd like to understand the next lines:
firstSundayOfOctober = Date.StartOfWeek(#date(Date.Year(date), 10, 7), Day.Sunday),
firstSundayOfApril = Date.StartOfWeek(#date(Date.Year(date), 4, 7), Day.Sunday),
Does anybody can explain what means the last numbers 7 in each line?. Date.StartOfWeek(#date(Date.Year(date), 10, 7) <---
Thank you in advance.
It's just defining a date: The 7th of October and the 7th of April, and then using Date.StartOfWeek to get the date that is the beginning of the week in which that date falls.
There's no way the beginning of the week in which the 7th of the month falls will be anything but the first Sunday of the month.
Actually, below is a function that is even simpler than what has been posted here. Someone on YouTube gave instructions how to create a column with a formula to add the TimeZone, and then selected to convert the time from Local to UTC.
All I did was consolidate his logic into a simple function which you don't even need to worry about determining whether it is daylight savings time (DST) as Power BI already knows how to distinguish between it. So like the function that was provided previously, you only need to add a column with the invoke function method and it will return the datetime in your local timezone. I also added the ability for the function to accept nulls, as some of the columns I was working with are optional and aren't always populated.
Hope this is helpful.
The simple formula is:
(datetimecolumn as nullable datetime) =>
let
DateTimeAddZone = DateTime.AddZone( datetimecolumn, 0 ),
DateTimetoLocal = DateTimeZone.ToLocal( DateTimeAddZone ),
DateTimeRemoveZone = DateTimeZone.RemoveZone( DateTimetoLocal ),
UTC_To_Local = DateTimeRemoveZone
// UTC_To_Local = DateTimeZone.RemoveZone( DateTimeZone.ToLocal(DateTime.AddZone( datetimecolumn, 0 )))
in
UTC_To_Local
Used this one to transfer from UTC over to AEST in Brisbane (Aus time). Worked a treat as is, thanks so much for this!
Be aware that if you move this version up to the Power BI Service, unless they have added the ability to set the TimeZone for the Power BI Service, it will think the local time is UTC.
Please take a look at my code at https://community.powerbi.com/t5/Power-Query/Convert-Date-Time-in-UTC-to-Local-Time-with-Daylight-sa.... Please make sure to run the testing script to make sure it is switching correctly specifically on the DST day and time, and if there are problems let me know and we can tweak it from there.
Thanks gpoggi!! this worked perfectly to resolve my time issue when power BI service refreshed my data!!
Thank you so much @gpoggi This works perfectly and it's also improved my understanding of custom functions in Power Query. My local time zone is NZST and being in the southern hemisphere our daylight savings spans the new year period, so I have to change the formula in isSummerTime slightly.
(datetimecolumn as datetime) =>
let
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
lastSundayOfSeptember = Date.StartOfWeek(#date(Date.Year(date), 9, 30), Day.Sunday),
firstSundayOfApril = Date.StartOfWeek(#date(Date.Year(date), 4, 7), Day.Sunday),
isSummerTime =
(date = lastSundayOfSeptember and time >= #time(2,0,0))
or
(date > lastSundayOfSeptember)
or
(date < firstSundayOfApril)
or
(date = firstSundayOfApril and time >= #time(3,0,0)),
timeZone = 12 + Number.From(isSummerTime),
NZST =
DateTime.From(datetimecolumn) + #duration(0, timeZone, 0, 0)
in
NZST
Hey thanks for posting that @willpage , I used your mod to get Australian Eastern time and it works a treat.
Can you please post the M language for Australian Eastern time ?
Is your source time coming in UTC? If so, try my simplified m query I posted on 05/12/21, which I have pasted below.
Please let me know if that solves your issue.
--------------------------
(datetimecolumn as nullable datetime) =>
let
DateTimeAddZone = DateTime.AddZone( datetimecolumn, 0 ),
DateTimetoLocal = DateTimeZone.ToLocal( DateTimeAddZone ),
DateTimeRemoveZone = DateTimeZone.RemoveZone( DateTimetoLocal ),
UTC_To_Local = DateTimeRemoveZone
// UTC_To_Local = DateTimeZone.RemoveZone( DateTimeZone.ToLocal(DateTime.AddZone( datetimecolumn, 0 )))
in
UTC_To_Local
Thanks. Source time is in UTC. Have you tested to publish to PowerBI services ? I have used ToLocal and worked perfectly using PowerBI desktop. Once it was published in PowerBI services, no longer working.
Comments in other article :
ToLocal works great as long as users are in Power BI Desktop in Pacific Time. However, as soon as the model publishes to the Power BI Service, and the data refreshes, the date-times are no longer be Pacific Time. ToLocal() converts to the local time of the Power BI servers which are set for Universal Coordinated Time.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
21 | |
18 | |
12 |