Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Add timezone hour to existing date/time/timezone column

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,

Need Help
  • collinsg's avatar
    collinsg
    3 years ago

    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.

     

4 Replies

  • collinsg's avatar
    collinsg
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        collinsg
        Icon for Solution Sage rankSolution Sage
        3 years ago

        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.