Forum Discussion
Solved
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...
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
isSummerTime =
(datetimecolumn > ForwardDate & #duration(0,2,0,0))
or
(datetimecolumn < BackDate & #duration(0,2,0,0)),That doesn't work, they're not compatible.
I tried it with + instead of & and it also doesn't work for a different reason: