Help With query

[Tango2310]

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 Rows",{{"utit_quotefinaliseddate", type DateTime.AddZone(["utit_quotefinaliseddate"],10)}}))

Thank in advance for any assistance

Todd 

[ronrsnfld]

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]

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]

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

[Anonymous]

Pretty sure, you need to be using Table.TransformColumns, not Table.TransformColumnTypes. Also, remove the word "type".

--Nate