Outlook 365 email timestamps

[Believer]

I am retrieving Outlook 365 emails via Exchange.Contents("<email address>").  When I look at the emails in outlook 365 on my desktop or online (outlook.office.com) I can see the date/time sent/received in my local time zone, UTC-5 or UTC-4 during daylight savings, e.g. 7/20/2021 4:49 PM.  However when these values come into a Power BI dataflow (and passed to the desktop data set) they're in the UTC+0 time zone.  In this case I am seeing 7/20/2021 8:49 PM.  How can I get the dataflow to show in my local time?  I was simply going to add hours to it myself, but this won't account for daylight savings.

[bernardoc]

A similar issue has been troubling me for a long time and I couldn't make this solution to work to me. Clearly, this is a PBI service issue which we are trying to address with complex solutions. Much more simple would be for service to request a desirable time zone to be observed, taking into account DST etc. 

[Believer · Accepted solution]

I've done a lot more research and experimenting.  Ultimately I really like the solution(s) put forth here: https://p3adaptive.com/2019/01/dst-refresh-date-function-power-bi-service/.

The elegance lies in the timeless, flexible, and very simple DST determination equations... which are very similar to what I was also working on.  No manual tables to maintain, etc.

Seems this happens often enough that the MS Power Query team should consider adding some more functions/features to make it a lot easier.  For starters, the UI should be able to display times in the local user's time zone.  Would be awesome to be able to name/reference time zones in Power Query too... such as "EST" etc.  Then we don't have to concern ourselves with time of year, hours offset...

[Believer]

I have made some progress but run into another issue.  After some digging and experimenting it came down to using DateTimeZone.RemoveZone(DateTimeZone.ToLocal(DateTime.AddZone([Created],0))) per this post: https://community.powerbi.com/t5/Power-Query/DateTimeZone-issue/m-p/1461323.  What had me stumped for the longest time was that I kept trying to get this to work in the dataflow, but in the dataflow “UTC” and “Local” are the same time zone because it's still in the cloud.  When I moved the transformations to the dataset on my local machine it worked as expected.  However… When the dataset refreshes online it’s broken again because we're back in the cloud where UTC and Local are the same time zone…

[GilbertQ]

Hi @Believer 

This would be because all date time is stored in UTC, so you will need to offset to your timezone to get the date time to show correctly.

[Believer]

First, I realized that although my PC time zone is "UTC-5" I am currently UTC-4 due to daylight savings.  I removed this disparity from my original question.

Also, I cannot simply offset the timezone by a fixed amount.  Most of the year I am UTC-5, but the rest of the year I am UTC-4... how can I know which dates to adjust by 4 vs 5 hours?