cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft

@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

18 REPLIES 18
JoeFox
Advocate I
Advocate I

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.

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

lmandrade
Frequent Visitor

Thanks, I'll try it this way.

fhendrick
Advocate I
Advocate I

Hi,

 

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

 

Hope it helps !

ryanshane
Frequent Visitor

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

View solution in original post

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
Microsoft

@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

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.

I will test your option as well. Grateful for the support!

ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

lmandrade
Frequent Visitor

I'll try. It looks like it might work. I'll be back here to tell if it worked ! Thank you.

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors