Convert utc to local time zone using Power Query

[ericOnline]

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:

• 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!

[hinteadan]

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-quer...

[v-danhe-msft]

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

[ericOnline]

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

[v-danhe-msft · Accepted 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

[ericOnline]

Hi @hinteadan. Not “complete” in all use cases, but it is “correct”. Key word being time “zone”. If you needed to convert to local “time”, then yes, an additional column for DST is required.
Have you found a solution for this additional parameter?

[hinteadan]

Hello @ericOnline, you are right, it's rather incomplete not incorrect. DST is a sneaky **bleep** which we tend to forget about, but actually want to consider when converting timestamps. I removed my original reply and posted one with the URL for the solution that includes DST.

[ericOnline]

Hi @hinteadan. You’re right! I haven’t shared the app with anyone outside my time zone so didn’t encounter the issue pointed out in the blog. Great catch. Thanks for posting this!

[ericOnline]

@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 🙂 )

[ericOnline]

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

[HotChilli]

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.

[ericOnline]

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