Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |