Forum Discussion
Convert utc to local time zone using Power Query
lmandrade You can use DateTime.AddZone function to achieve your requirement. This function adds the timezonehours as an offset to the input datetime value and returns a new datetimezone value.
DateTime.AddZone([CreatedOn],3)
Reference
https://msdn.microsoft.com/en-us/library/mt253514.aspxOpss not get it! I have a column called [ActualCloseDate] I need to adjust down in 3 hours all your data bringing the time of Brazil in a new column. I began to mount the formula but there are so many examples and figures that just getting confused! Could help me with the logic?!?
I had the same problem. It's unfortunate that there's no proper native support for converting between timezones.
This is what i did for a quick and simple converstion of a UTC column to a new AEST column, taking DST into consideration:
- Open Query Editor
- Add a new column, and specify the m query below. The below will take the "Created" date in UTC, get the GMT offset based on the DST change-over dates in UTC for my region, and then pass that into DateTimeZone.SwitchZone to get the "Created" date in AEST.
- Set the data type to Date
// Convert "Created" date from UTC to AEST
let createdString = Number.ToText(Date.Year([Created]))
& Text.PadStart(Number.ToText(Date.Month([Created])), 2, "0")
& Text.PadStart(Number.ToText(Date.Day([Created])), 2, "0")
& Text.PadStart(Number.ToText(Time.Hour([Created])), 2, "0"),
createdNum = Number.FromText(createdString),
// AEST time: get the offset depending on whether it's daylight savings time or not (GMT+11 or GMT+10) by looking at the DST change-over times in GMT 0
timeZoneOffsetAEST = if createdNum >= 2017093016 and createdNum < 2018033117
then "11"
else
if createdNum >= 2018100616 and createdNum < 2019040617
then "11"
else
if createdNum >= 2019100516 and createdNum < 2020040417
then "11"
else
if createdNum >= 2020100316 and createdNum < 2021040317
then "11"
else "10",
createdInAEST = DateTimeZone.SwitchZone(DateTimeZone.From([Created]),Number.FromText(timeZoneOffsetAEST),0)
in createdInAEST
There are better solutions where people are importing a table of GMT offsets from excel spreadsheets, or by querying a web service, since powerBI is able to query web. I just needed a quick and simple solution that would work well enough for the next few years, so i just hard coded the DST times (GMT+11) times for the next few years to get the right GMT offset.
I have put in a feature request for PowerBI to natively support timezone and DST conversion. Please upvote if you'd like to see this happen: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33732571-time-zone-conversion-accounting-for-daylight-savin
