How do I create a custom global function? (for time zone conversion)

[Domenick]

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.

[jdbuchanan71 · Accepted solution]

@Domenick 

You can add a custom column in PowerQuery to adjust the UTC back like this.

[Date] + #duration(0,-8,0,0)

[jdbuchanan71]

Nice, thank you for sharing your final solution @Domenick .

[Domenick · Accepted solution]

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

[Rickmaurinus]

Domenick, 

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.

[jdbuchanan71]

No, because UTC is not affected by daylight saving time.

[jdbuchanan71 · Accepted solution]

@Domenick 

You can add a custom column in PowerQuery to adjust the UTC back like this.

[Date] + #duration(0,-8,0,0)

[Domenick]

Will that account for daylight savings?