Local Datetime to UTC, inconsistent behavior from DateTimeZone.From()

[mr_fnord]

I have a datatable from a SQL database with a local datetime column [DeliveryDateTime] that I need to join to other tables with UTC times. 

I am using a Custom Column with the formula:

DateTimeZoneLocal = DateTimeZone.From([DeliveryDateTime])

When I add this step, I get a result that treats [DeliveryDateTime] as a UTC time:

which is not what the documentation describes.

If I add another Custom column step, either before or after the DateTimeZone.From step, suddenly the DateTimeZone.From starts assigning the local timezone.

Added column:

Custom= DateTime.AddZone([DeliveryDateTime], -11) (arbitrary datetimezone function, offset doesn't matter, before or after the From function doesn't matter)

Result:

If I remove the Custom column in another step, the DateTimeZoneLocal resets back to UTC (+0).

Can anyone else recreate this behavior? Is there any way to describe this except as a bug?

[selimovd]

Hey @mr_fnord ,

I think you have to put the "cultural" parameter for the DateTimeZone.From function:

DateTimeZoneLocal = DateTimeZone.From([DeliveryDateTime], "en-US")

Otherwise Power Query doesn't know which local date this is.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[mr_fnord]

Adding a culture works, but why the inconsistent behavior based on other datetimezone functions?

[selimovd]

Hey @mr_fnord ,

what do you see as inconsistent behavior?

Best regards

Denis