Chris Webb shows how to convert a datetime from UTC to your local time zone using M:
Here’s a brief explanation of what the query does:
First it reads the times from the Excel table and sets the Time column to be datetime data type
It then creates a new column called UTC and then takes the values in the Time column and converts them to datetimezone values, using the DateTime.AddZone() function to add a time zone offset of 0 hours, making them UTC times
Finally it creates a column called Local and converts the UTC times to my PC’s local time zone using the DateTimeZone.ToLocal() function
There are some limitations to what it does, so you can’t convert to just any time zone while still retaining Daylight Savings Time awareness.