Forum Discussion
Changing DateTime Value from UTC to AEDT
Hi all,
I have extracted data from Salesforce Objects as a datasource and noticed for whatever reason the datetime values for dates (Created, Last Modified etc.) is reverting from my timezone (AEST/AEDT) to UTC. Once I figured this out I did a simple transformation to correct to AEST however forgot about AEDT. After returning from leave I realised my corrected date values were now 1 Hour Behind. I have seen some other forums mention transformation queries and I have tried to implement one as shown below. However this is not working and is still returning only the AEST modified Date. My post I suppose is 2 fold
1. Has anyone experienced the Power Query issue of A Salesforce Object DateTime being changed to UTC
2. Can anyone identify why this below query is not working.
= (datetimecolumn as datetime) =>
let
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
firstSundayOfOctober = Date.StartOfWeek(#date(Date.Year(date), 10, 1), Day.Sunday),
firstSundayOfApril = Date.StartOfWeek(#date(Date.Year(date), 4, 1), Day.Sunday),
isSummerTime = (date = firstSundayOfApril and time >= #time(2,0,0))
or
(date > firstSundayOfApril and date < firstSundayOfOctober)
or
(date = firstSundayOfOctober and time <= #time(2,0,0)),
timeZone = 11 + 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
Your analysis is incorrect.
lt is not a function. Rather it transforms each datetime in your date time column from UTC datetimes to your local timezone time, be it standard time or daylight savings.
You did not provide a usable data sample, so the Source line is there only to create something. I inferred from your question that you had a column of datetimes.
My local timezone is EST (US-East coast).
Please note that the dates are showing as MDY (US style), not DMY.
Our change date from EDT to EST is on the first Sunday in November at 2AM which happens to be Nov 3 2024.
Perhaps it would be more clear if I showed it to you as an additional column
let Source = Table.FromColumns( {List.DateTimes(#datetime(2024,11,1,12,0,0), 24*7,#duration(0,1,0,0))}, type table[DateTimeUTC=datetime]), #"to Local Time" = Table.AddColumn(Source, "DateTimeLocal", each DateTime.From( DateTimeZone.ToLocal( DateTime.AddZone([DateTimeUTC],0))),type datetime) in #"to Local Time"will produce:
Note the change from EDT to EST at 2 AM on Nov 3 in the 2nd column.
A similar result should occur in Australia.
In the example code, I used the Source step to create a column of datetimes. In your code, you should insert the #"to Local Time" step at some point in your code after your datetime column has been created. You can either the version that transforms the column itself, or the one that adds an additional column.
It is only that #"to Local Time" step that is relevant to your question.
The Source step serves only to create a data sample, since you did not provide one.
In the #"to Local Time" step, you may need to change the Source reference and the column name(s) to match whatever you have.
6 Replies
ronrsnfldSuper User
Since you are converting to your local time zone, rather than your function, you can use the DateTimeZone.ToLocal function
let Source = Table.FromColumns( {List.DateTimes(#datetime(2024,11,1,12,0,0), 24*7,#duration(0,1,0,0))}, type table[DateTime=datetime]), #"to Local Time" = Table.TransformColumns(Source, {"DateTime", each DateTime.From( DateTimeZone.ToLocal( DateTime.AddZone(_,0))),type datetime}) in #"to Local Time"There seem to be numerous problems with your query, but I did not look into it in detail, so could be wrong about a few things.
- The first Sunday of a month is NOT the first Sunday of the week that includes the first Day of the month.
- If Oct 1 happens to be on a Monday (or later), the first day of that week would be in September.
- Your switch to DST occurs when LOCAL datetime is between certain values; you appear to be checking the UTC datetime in your query.
I have no experience with SalesForce
Hi There,
I have attempted to use this code and the function produces a list of dates from 01/11/2024 to the 08/11/2024. Would you be able to ellaborate on implementation of the function?After reviewing the query I understand it is creating the date/time for the local time for 7 days from the start date. This doesn't help my use case as
1. The above query I have based on from modifying queries in previous posts. I need this to be a function or equivalent as In my base tables I have date values for cases that are coming through as UTC that I need to transform to my timezone accounting for daylight savings.
2. If I create the table using your query, it won't relate back to my tables. I could potentially have the column being UTC and then relate the values to the transformed AEST/AEDT time. But I am not sure how this would then impact my report with Filtering Reporting etc- ronrsnfld
Your analysis is incorrect.
lt is not a function. Rather it transforms each datetime in your date time column from UTC datetimes to your local timezone time, be it standard time or daylight savings.
You did not provide a usable data sample, so the Source line is there only to create something. I inferred from your question that you had a column of datetimes.
My local timezone is EST (US-East coast).
Please note that the dates are showing as MDY (US style), not DMY.
Our change date from EDT to EST is on the first Sunday in November at 2AM which happens to be Nov 3 2024.
Perhaps it would be more clear if I showed it to you as an additional column
let Source = Table.FromColumns( {List.DateTimes(#datetime(2024,11,1,12,0,0), 24*7,#duration(0,1,0,0))}, type table[DateTimeUTC=datetime]), #"to Local Time" = Table.AddColumn(Source, "DateTimeLocal", each DateTime.From( DateTimeZone.ToLocal( DateTime.AddZone([DateTimeUTC],0))),type datetime) in #"to Local Time"will produce:
Note the change from EDT to EST at 2 AM on Nov 3 in the 2nd column.
A similar result should occur in Australia.
In the example code, I used the Source step to create a column of datetimes. In your code, you should insert the #"to Local Time" step at some point in your code after your datetime column has been created. You can either the version that transforms the column itself, or the one that adds an additional column.
It is only that #"to Local Time" step that is relevant to your question.
The Source step serves only to create a data sample, since you did not provide one.
In the #"to Local Time" step, you may need to change the Source reference and the column name(s) to match whatever you have.
- The first Sunday of a month is NOT the first Sunday of the week that includes the first Day of the month.
