Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
lmandrade
Frequent Visitor

Convert utc to local time zone using Power Query

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.

2 ACCEPTED SOLUTIONS
v-caliao-msft
Microsoft Employee
Microsoft Employee

@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

View solution in original post

lmandrade
Frequent Visitor

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

Ashampoo_Snap_2016.06.28_08h55m28s_001_.png

View solution in original post

23 REPLIES 23
JoeFox
Advocate II
Advocate II

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.

It works perfetctly! Thanks!

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

Thanks, I'll try it this way.

Anonymous
Not applicable

Hi,

 

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

 

Hope it helps !

Thanks! It works wonders!

This is perfect! Thanks!

This works for me. Thank you!

Anonymous
Not applicable

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.

 

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
Frequent Visitor

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

Ashampoo_Snap_2016.06.28_08h55m28s_001_.png

Anonymous
Not applicable

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

It doesn't work for me either. 😞

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
Microsoft Employee
Microsoft Employee

@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

thank you

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.

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

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!

 

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.