Convert Date/Time in UTC to Local Time with Daylight savings

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

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

gpoggi  thank you for the code, I understand the logic behind it and it should work. I am not sure how to create a function with the code and how to enable it to modify the column I want. Could you please provide step-by-step instructions? I am still new to the Power Query Editor. 

Thanks gpoggi!! this worked perfectly to resolve my time issue when power BI service refreshed my data!!

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.

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

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.

gpoggi 

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

Tried this but date column looks like below with "-5:00" and getting error in the new column - thoughts on what might be wrong?

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!