Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to 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
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?!?
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.
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!
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:
// 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...
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?!?
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.
@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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
106 | |
64 | |
60 |