Forum Discussion
onlycallisto
2 years agoFrequent Visitor
Timezone conversion issues in PowerQuery
Hi, I am having essentially this same problem, but the solution from this post is not working for me: https://community.fabric.microsoft.com/t5/Power-Query/Convert-Date-Time-in-UTC-to-Local-Time-wi...
- 2 years ago
I've figured it out!
It's because NZ summer wraps around the new year.So I need to calculate WinterTime instead and adjust the calculation like so:
(datetimecolumn as datetime) => let date = DateTime.Date(datetimecolumn), time = DateTime.Time(datetimecolumn), ForwardDate = Date.StartOfWeek(#date(Date.Year(date), 9, 30), Day.Sunday), BackDate = Date.StartOfWeek(#date(Date.Year(date), 4, 7), Day.Sunday), isWinterTime = (date = BackDate and time >= #time(2,0,0)) or (date > BackDate and date < ForwardDate) or (date = ForwardDate and time < #time(2,0,0)), timeZone = 13 - Number.From(isWinterTime), NZ_time = DateTime.From(date) + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time)) + #duration(0, timeZone, 0, 0) in NZ_time
onlycallisto
2 years agoFrequent Visitor
I've figured it out!
It's because NZ summer wraps around the new year.
So I need to calculate WinterTime instead and adjust the calculation like so:
(datetimecolumn as datetime) =>
let
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
ForwardDate = Date.StartOfWeek(#date(Date.Year(date), 9, 30), Day.Sunday),
BackDate = Date.StartOfWeek(#date(Date.Year(date), 4, 7), Day.Sunday),
isWinterTime =
(date = BackDate and time >= #time(2,0,0))
or
(date > BackDate and date < ForwardDate)
or
(date = ForwardDate and time < #time(2,0,0)),
timeZone = 13 - Number.From(isWinterTime),
NZ_time =
DateTime.From(date)
+ #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))
+ #duration(0, timeZone, 0, 0)
in
NZ_time