Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Tango2310
Helper I
Helper I

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

Thank in advance for any assistance

 

Todd 

4 REPLIES 4
ronrsnfld
Super User
Super User

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:

ronrsnfld_0-1723681807585.png

Change to EDT:

ronrsnfld_1-1723681847854.png

 

Change to EDT and add time zone specifier:

ronrsnfld_2-1723681879791.png

 

 

 

 

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?

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

Anonymous
Not applicable

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

 

--Nate

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.