Add timezone hour to existing date/time/timezone column

[Anonymous]

Hello there!

I'm struggling to add the timezone part of this column to the existing hour. I want to display it in Date/Time type but when converted to it, timezone doesn't add.

Is there any easy way to make this? Or should I transform it into decimal numbers, make the addition and then return to date/time?

Thank you for your time and patience,

[collinsg · Accepted solution]

Good day @Anonymous ,

If I have understood your requirement you wish to transform 3/16/2021 10:39:00 +01:00 to 3/16/2021 11:39:00, the following should meet that requirement. The method is

• Remove the zone from the datetimezone.
• Find the hours of the datetimezone and use the hours as the hour part of a duration.
• Add the duration to the datetime.

Here is the M code - you can insert a step and paste this cut or open the advanced editor and paste it. Just change "#Previous Step" to the name of your previous step.

= Table.TransformColumns( 
     #"Previous Step",
     {
        "opened_dt", 
        each DateTimeZone.RemoveZone(_) + #duration( 0, DateTimeZone.ZoneHours(_), 0, 0 ),
        type datetime
     }
  )

Hope this helps and if it is a solution please mark it "solved" so others may find it.

[Anonymous]

Hello @collinsg!

That is an amazing way to do it, thanks! I was going the same way but I lacked those two functions. It works perfectly, although in the column there are some null values and I am trying to avoid the error message.

I tried a simple if clause but it doesn't work, is it because it can't compare null to a DateTimeZone column?

= Table.TransformColumns(Timezone,{
"original_closed_dt", each if [original_closed_dt] is null then null 
else DateTimeZone.RemoveZone(_) + #duration( 0, DateTimeZone.ZoneHours(_), 0, 0 ),type datetime})

[collinsg]

Good day @Anonymous ,

I'm glad that helped get you closer to a solution. A small change will get rid of the error message. Replace [original_closed_dt] after "if" with an underscore, as below.

= Table.TransformColumns(
     #"Previous Step",
    {
       "original_closed_dt", 
       each 
          if _ is null then null 
          else DateTimeZone.RemoveZone(_) + #duration( 0, DateTimeZone.ZoneHours(_), 0, 0 ),
       type datetime
    }
 )

In this context the underscore represents the current row of column "orginal_closed_dt" (whereas [original_closed_dt] represents the whole column).

All the best. 

[Anonymous]

Update:

I just tried using if DateTimeZone.From(_) instead of the column name and it worked. Thank you @collinsg for being so helpful