Excel Power Query - convert date time from UTC to Local

Question

I'm connecting to an MS SQL database via Power Query (Version: 2.10.3598.81) in Excel 2010.

My dates are stored in UTC in the database, however I would like to show them as local in the spreadsheet.

DateTimeZone.From() - converts to a DateTimeZone type but assumes the input date is in the Local timezone. This is the exact opposite of what I want.

DateTimeZone.ToLocal() - gives an error, I think because there's no timezone information in the source date.

Local in my case is Australian EST, however, it would be great if Excel just picked up the local timezone. (It appears to do this already)

Answer 1

The DateTime.AddZone() function is used to convert between time zones, and can be leveraged to add timezone information to an existing date. If your date is UTC you would use DateTime.AddZone([date_created],0) and if your date was already in AEST then you would use DateTime.AddZone([date_created],10).

In this way, we will use it:

DateTimeZone.ToLocal(DateTime.AddZone([date_created_UTC],0))