Power Query is shifting time zones, how to fix?

[DoctorYSG]

The source datalake is sending a calander table, with days as date format, and hours in ISO with timezone (Zulu, aka GMT).

But PowerBI is reading the data with an EST time zone, so the day and the hour are not aligned. How can I fix that?

[Fowmy · Accepted solution]

@DoctorYSG 

This video should be useful for you: https://youtu.be/0f4-R3jQWRA
More details: DateTimeZone.SwitchZone - PowerQuery M | Microsoft Learn

[DoctorYSG]

Correct, it can be done with M-Query. But I would still call this a bug, if what is arriving at PowerBI is an ISO format timestamp with a time zone (ZULU) and PowerBI is not understanding the time zone. Right?

[DallasBaba]

@DoctorYSG you can use the DateTimeZone.SwitchZone function. Here are the steps to convert the time zone from EST to GMT. Kindly FOllow these steps:
1 - Convert the date and time values to a datetimezone value using the DateTimeZone.From function.
2 - Use the DateTimeZone.SwitchZone function to change the time zone from EST to GMT.
3 - Convert the datetimezone value back to a date and time value using the DateTimeZone.ToLocal function.

let
Source = <your data source>,
#"Converted to DateTimeZone" = Table.TransformColumns(Source,{{"Date", each DateTimeZone.From(_, "Eastern Standard Time")}}),
#"Switched Time Zone" = Table.TransformColumns(#"Converted to DateTimeZone",{{"Date", each DateTimeZone.SwitchZone(_, 0, -5)}}),
#"Converted to Local Time" = Table.TransformColumns(#"Switched Time Zone",{{"Date", each DateTimeZone.ToLocal(_)}})
in
#"Converted to Local Time"

The code above assumes that the date and time values are stored in a column named Date.

Ensure to replace <your data source> with the name of your data source.

You can also Watch Curbal YouTube on how to Convert UTC datetime to local time zones in Power Query
https://www.youtube.com/watch?v=M1zquwmpnZE

Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
Note: 

If this post helps, please consider Accepting it as the solution to help others find it more quickly. OR
If my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Best Regards,

Dallas.

[DoctorYSG]

Should this not be a bug report? An ISO format date/time with a Z at the end should be parsed as ZULU.