Forum Discussion

Tango2310's avatar
Tango2310
Icon for Helper I rankHelper I
2 years ago

Help With query

Hi, I am trying to transform a utc date time column to a specific timezone by using DateTime.Add Zone but cant quite get the syantax right.   My code is = Table.TransformColumnTypes(#"Filtered R...
Need Help
ronrsnfld's avatar
ronrsnfld
Icon for Super User rankSuper User
2 years ago

DateTime.AddZone does not change the underlying time. It merely adds a TimeZone segment with the stated hours offset.

To change a UTC time to the corresponding time in a specific time zone, you need to add/subtract the number of hours.

If you also want to add a time zone specifier, that is a separate operation.

 

For example, to change UTC time to Eastern Daylight Time:

Table.TransformColumns(Source, {"Date", each _ + #duration(0,-4,0,0), type datetime}),

To change UTC time to Eastern Daylight Time and also add a timezone identifier:

Table.TransformColumns(Source,{"Date", each DateTime.AddZone(_ + #duration(0,-4,0,0),-4), type datetimezone})

Original UTC Time:

Change to EDT:

 

Change to EDT and add time zone specifier:

 

 

 

 

  • Tango2310's avatar
    Tango2310
    Icon for Helper I rankHelper I
    2 years ago

    Thanks for your help ronrsnfld 

     

    Looks like you cant execute this type of query on a Direct Query.

     

    If that is the case, would it be then safe to say that if your using a datasource of Dataverse with any column that has a date, you would need to always use Import during your Transform process?

    What is best practice in this type of scenario?

    • ronrsnfld's avatar
      ronrsnfld
      Icon for Super User rankSuper User
      2 years ago

      I'm sorry. Those questions are outside my area or expertise.