Convert utc to local time zone using Power Query

Not sure if new commands have been added to Power BI since this discussion but here's what I would do if you had a 'Created' column in UTC DateTime format and wanted this to show in any user's local time zone.

1. Create a new column and add 'Zone' to your Date Time column e.g. DateTimeZone = DateTime.AddZone([Created],0)

2. Create a new column and add 'Local' to your new 'DateTimeZone' column e.g LocalDateTime = DateTimeZone.ToLocal([DateTimeZone])

3. Create a new column to remove the 'Zone' from your new 'LocalDateTime' column e.g. CleanLocalDateTime = DateTimeZone.RemoveZone([LocalDateTime])

This should get you from a UTC DateTime format column to a user specific localised DateTime format column. You can probably do all this in one new column, but I like to break things apart and do it bit by bit to clearly show what's going on at each step.

Hope it helps.

Hi,

Please try DateTimeZone.RemoveZone(DateTimeZone.ToLocal(DateTime.AddZone([#"Date_UTC"],0)))

Hope it helps !

Is this what you need?: https://msdn.microsoft.com/en-us/library/mt253522.aspx

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:

1. Open Query Editor
2. 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.
3. 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.