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

I think I've discovered the answer.

The function DateTime.AddZone() which I thought was used to convert between timezones is actually used 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).

So I can now use:

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

and Power Query will correctly convert my date created from UTC to AEST (or whatever is local).