Forum Discussion
How do I create a custom global function? (for time zone conversion)
I want to write a custom function to convert datetime from UTC to PST/PDT. Can someone point me in the right direction? I want to use the function across multiple reports. The only guidance I've found is file-specific.
Also would be cool if you just knew of an already existing function that easily converts to a different timezone; that would be great.
You can add a custom column in PowerQuery to adjust the UTC back like this.
[Date] + #duration(0,-8,0,0)I solved this by adding a Custom Column in Query Editor which brings the time in as UTC and converts it to PST or PDT based appropriately. The -7 and the -8 are the offset hours (Pacific time is either 7 or 8 hours behind UTC depending on the time of year). I strung this solution together with the help of this guidance by Rickmaurinus : https://gorilla.bi/power-query/last-refresh-datetime/
let
UTC_DateTimeZone = DateTimeZone.UtcNow(),
UTC_Date = Date.From(UTC_DateTimeZone),
StartSummerTime = Date.StartOfWeek(#date(Date.Year(UTC_Date), 3, 31), Day.Sunday),
StartWinterTime = Date.StartOfWeek(#date(Date.Year(UTC_Date), 10, 31), Day.Sunday),
UTC_Offset = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then -7 else -8,
PST_Timezone = DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset)
in
PST_Timezone
6 Replies
I solved this by adding a Custom Column in Query Editor which brings the time in as UTC and converts it to PST or PDT based appropriately. The -7 and the -8 are the offset hours (Pacific time is either 7 or 8 hours behind UTC depending on the time of year). I strung this solution together with the help of this guidance by Rickmaurinus : https://gorilla.bi/power-query/last-refresh-datetime/
let
UTC_DateTimeZone = DateTimeZone.UtcNow(),
UTC_Date = Date.From(UTC_DateTimeZone),
StartSummerTime = Date.StartOfWeek(#date(Date.Year(UTC_Date), 3, 31), Day.Sunday),
StartWinterTime = Date.StartOfWeek(#date(Date.Year(UTC_Date), 10, 31), Day.Sunday),
UTC_Offset = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then -7 else -8,
PST_Timezone = DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset)
in
PST_TimezoneDomenick,
I think the function you used is the easiest approach. At this point we can't make it a global function though. I hope the Microsoft Team will release a functionality that does support this in the future.
Until then, save the query somewhere or copy it from the blogpost 🙂
Enjoy!
Rick
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
You can add a custom column in PowerQuery to adjust the UTC back like this.
[Date] + #duration(0,-8,0,0)Will that account for daylight savings?
No, because UTC is not affected by daylight saving time.
Nice, thank you for sharing your final solution Domenick .
