Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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
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:
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.
Pretty sure, you need to be using Table.TransformColumns, not Table.TransformColumnTypes. Also, remove the word "type".
--Nate
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |