Forum Discussion
Convert utc to local time zone using Power Query
Hello,
Does anyone have a better example for how to add hours (DateTime.AddZone) to a date-time value? The docs are not clear on this:
DateTime.AddZone(#datetime(2010, 5, 4, 6, 5, 5), 8) equals #datetimezone(2010, 5, 4, 6, 5, 5, 8, 0)
I have no idea what all the numbers shown in the "example" are supposed to represent. I have a column (EVENT_DATE) of date/time values like "2018 05/05/2018 05:00:00 AM" that I need to add a certain # of hours to BASED ON THE VALUE IN THEIR RESPECTIVE ID_NUM COLUMN.
Example:
- If ID_NUM = 1, then add 2 hours to all times in the EVENT_DATE column.
- If ID_NUM = 2, then add 1 hour to all times in the EVENT_DATE column
- etc.
Any ideas on how to accomplish this using the Advanced Editor? Ideally I want to script this in with my transformation.
Thank yoU!
Hi ericOnline,
Based on my test, you could refer to below steps in query editor:
Add custom column:
if [ID]=1 then [Date]+#duration(0,2,0,0) else if [ID]=2 then[Date]+#duration(0,1,0,0) else null
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
11 Replies
- The current solution IS NOT correct, it doesn't take Daylight Saving Time into account. The following blog post describes the correct solution: https://seddryck.wordpress.com/2018/03/22/converting-datetime-from-utc-to-local-time-with-power-query-m-language/
Hi ericOnline,
Based on my research, you could refer to below link to use the DateTime.AddZone function:
https://community.powerbi.com/t5/Desktop/Convert-UTC-to-client-time-zones/td-p/56337
Regards,
Daniel He
Hello v-danhe-msft. Thank you for the link. I believe I read through this post (and a few others on the topic) yesterday before posting.
The post begins with "...Assuming currently we have the Time and UTC column in a table, where [Time] stores the actual UTC time, [UTC] stores the changed hour count between client and UTC."
I don't have a dedicated table with a Time and UTC column. The data table I'm operating on does have a Time column in UTC format, but it is amongst 12 other columns.
Do I need to create a dedicated TimeTable to make the translations work?
Thank you
I think you want to create durations from your data and just add(+) them to your datetime.
yourdatetime + #duration()
duration is (dd,hh,mm,ss) so a duration of 1 hour is (0,1,0,0)
The datetimezone will create you a datetime (yy: mm: dd : hh : mm : ss) with an additional offset of hh : mm, which is not really what you want, I think.
