TimeZone conversions can be tricky, especially when DST applies. Unfortunately, there is not a good way to directly convert a DateTime to another zone (factoring in DST). However, there are free Rest APIs you can use that don't require an API key (and more free ones that do). Here are two examples.
If you only need to convert a time on today's date, you can simply get the offset value with this expression. You can remove the [utc_offset] part to see what other info is there. You can then use another function to add hours or switchzone by that number of hours (after you convert it to a number).
= Json.Document(Web.Contents("http://worldtimeapi.org/api/timezone/Africa/Cairo"))[utc_offset]
If you have a column of past/future datetimes and need to convert them (accounting for DST), you can use this function I wrote that uses a different REST API.
//fnConvertTimeZone
(dt as text, fromZone as text, toZone as text) =>
let
BaseURL = "https://www.timeapi.io/api/Conversion/ConvertTimeZone",
headers = [#"Content-Type" = "application/json"],
Body = [dateTime = dt, fromTimeZone = fromZone, toTimeZone = toZone, dstAmbiguity=""],
Response = Web.Contents(BaseURL, [Headers = headers, Content = Json.FromValue(Body)]),
Result = Json.Document(Response)[conversionResult][dateTime]
in
Result
You can then Invoke Custom Column passing in your DateTime and zone info like this. I named the function fnConvertTimeZone. There is likely a limit on this API, so it may not work with too many rows.
= fnConvertTimeZone("2021-03-14 17:45:00", "Europe/Dublin", "Africa/Cairo")
or with a column reference (with the text formatted as shown above)
= fnConvertTimeZone([DateTime Column], "Europe/Dublin", "Africa/Cairo")
Pat
Did I answer your question? Mark my post as a solution! Kudos are also appreciated! To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypa HoosierBI on YouTube
