Forum Discussion
Convert utc to local time zone using Power Query
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.aspxOpss 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?!?
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
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 will test your option as well. Grateful for the support!
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.
thank you
