Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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), 😎 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:
Any ideas on how to accomplish this using the Advanced Editor? Ideally I want to script this in with my transformation.
Thank yoU!
Solved! Go to Solution.
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
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
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
@v-danhe-msft, this is an excellect solution and worked very well. I'd recommend this for anyone looking to do TimeZone conversions in their data! (from my novice point of view 🙂 )
Aha! Interesting. This is the first time I've seen an IF statement used in M! (I'm very new). Thank you @v-danhe-msft and @HotChilli.
Hm. So taking this one step further: If I add a UTCOffset column to the data, how do I call this column in the query?
Example Table:
| UTCTime | UTCOffset | AdjLocalTime |
|------------|--------------|------------------|
| 12:00:00 | -5 | 07:00:00 |
Example:
If [ID]=1 then [Date] + #duration(0,[UTCOffset],0,0) else if [ID]=2 then [Date] + #duration(0,[UTCOffset],0,0) else null
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.
Hi @HotChilli. How is your suggested implemented?
I have a table of 1800 rows. Depending on the ID# column of each record I want to programmatically add/remove hours to a DateTime column that is in UTC format.
I'm new at PowerBI so need some more details.
Thank you
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
70 | |
38 | |
27 | |
26 |
User | Count |
---|---|
97 | |
96 | |
59 | |
44 | |
40 |