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

14 REPLIES 14
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
Regular 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

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 II
Super User II

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

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors