If you’re a Power BI or advanced Excel user you’ve almost certainly come across Power Query.
When you get data from SaaS applications you often have to deal with UTC timestamps, which for the purpose of a user-facing report, needs to be in local time, taking account of daylight savings. It’s not enough just to add 12 hours because half the year your timestamps will be an hour out.
I’m documenting this for my own reference more than anything because by the time I have to do this again I’ll have forgotten how to do it and forgotten which PBIX file to look in. Published to the web in case someone else wants to know.
Credit is due to gpoggi in this thread on the MS Power BI forums for the code and the method. I’ve adapted this to NZ time. Look in the thread for how to add the custom function and call it in a new column. No need to repeat that here.
Here’s the code:
//input of the function is called datetimecolumn and its type is datetime
(datetimecolumn as datetime) =>
let
//define a variable called date as the date component of the datetimecolumn input
date = DateTime.Date(datetimecolumn),
//define a variable called time as the time component of the datetimecolumn input
time = DateTime.Time(datetimecolumn),
//define the day that summer time starts as a variable called lastSundayOfSeptember.
//this is the start of the week in which the 30th of September falls of the year we get from the input
lastSundayOfSeptember = Date.StartOfWeek(#date(Date.Year(date), 9, 30), Day.Sunday),
//define the day that summer time ends as a variable called firstSundayOfApril.
//this is the start of the week in which the 7th of April falls of the year we get from the input
firstSundayOfApril = Date.StartOfWeek(#date(Date.Year(date), 4, 7), Day.Sunday),
//this gives us a boolean if the input date is in the summer time
isSummerTime =
(date = lastSundayOfSeptember and time >= #time(2,0,0)) //any time after 2am on the last Sunday in September
or
(date > lastSundayOfSeptember) //the date is after the last Sunday in September
or
(date < firstSundayOfApril) //the date is before the first Sunday in April
or
(date = firstSundayOfApril and time >= #time(3,0,0)), //the date is on the first Sunday in April prior to 3am
timeZone = 12 + Number.From(isSummerTime), //define timeZone as an integer that's 12 plus the numerical value of the boolean above
// (1 for summer 0 for not summer)
NZST =
DateTime.From(datetimecolumn) + #duration(0, timeZone, 0, 0) //take the original UTC date time and add the number of hours
//in timeZone, 12 for winter 13 for summer
in
NZST
William's IT Blog 