Convert utc to local time zone using Power Query

[lmandrade]

Would i like to convert a column [CreatedOn] Table [Opportunity] found in (UTC + 00: 00) to (UTC + 03: 00) Brazil. I need to create a new column to Power Query (Add Column). Can anyone help me? Thank you.

[v-caliao-msft · Accepted solution]

@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.aspx

[JoeFox]

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.

[francisco_marco]

It works perfetctly! Thanks!

[samdep]

Oh my gosh, I've been trying to figure this out for hours. This saved me, thank you!! 

[lmandrade]

Thanks, I'll try it this way.

[Anonymous]

Hi,

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

Hope it helps !

[notRonaldo]

Thanks! It works wonders!

[Luiggo07]

This is perfect! Thanks!

[PiKho]

This works for me. Thank you!

[Anonymous]

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.

[kionis]

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

[lmandrade · Accepted solution]

Opss 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?!?

[Anonymous]

Hi,

I've been using a similar conversion to this (DateTimeZone.ToLocal) in one of my scripts - but in the latest release it seems it seesed to work.

Is this something you can confirm from your experience?

Thanks.

/R

[Catalizt]

It doesn't work for me either. 😞

[lmandrade]

Unfortunately I did not use this function for the time conversion. In a later version of this post Power BI Desktop started to download my data in the original format -3: 00 GMT (Brazil) which was what I needed and therefore I did not have to try to do this conversion anymore. I even tried to do this conversion initially but I did not succeed.

[v-caliao-msft · Accepted solution]

@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.aspx

[nhathuoctinhyeu]

thank you

[JoeFox]

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.

[benjamin_sasin]

That's incorrect: this only adds the timezone reference to the existing DateTime, but doesn't actually add or substract hours.

[Catalizt]

I'm getting an error that says DateTime.AddZone isn't a valid table, variable, or function name.

Here is my column calculation:

Mitigated Date Local = DateTime.AddZone('IcM Incidents'[MitigateDate],-8)

Is there something else I should be adding?

Thank you!

[lmandrade]

With updates to PowerBi my data source, Dynamics CRM Online 2016, started to bring the correct date (GMT / UTC - 03:00) and in this way my problem ended up being resolved in an indirect way. Unfortunately I also tried to use the function to convert the spindle but it was not successful.