cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Catalizt Frequent Visitor
Frequent Visitor

Re: Convert utc to local time zone using Power Query

It doesn't work for me either. Smiley Sad

ryanshane Frequent Visitor
Frequent Visitor

Re: Convert utc to local time zone using Power Query

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.

 

Highlighted
kionis Frequent Visitor
Frequent Visitor

Re: Convert utc to local time zone using Power Query

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-acc...

fhendrick Frequent Visitor
Frequent Visitor

Re: Convert utc to local time zone using Power Query

Hi,

 

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

 

Hope it helps !