DatetimeOffset not converting to DateTime properly

[Anonymous]

In Power Query Editor, when you change the Type of DateTimeOffset to DateTime Power BI just truncate the Offset, instead of converting it.

Example,

Source in DateTimeOffset:

16/10/2018 15:58:15 +01:00

After Changing the type to DateTime:

16/10/2018 15:58:15

Expected Result:

16/10/2018 16:58:15

If you do the same for a date in the Z format it converts it correctly:

Source:

2018-10-16T15:58:14.9970789Z

After changing the Type to DateTime:

16/10/2018 16:58:15

[v-yuta-msft]

@Anonymous ,

Based on my test, currently power bi can't recognize the offset format of "datetimezone" type. As a workaround, I would suggest you to input following power query function to add the offset to datetime.

 = DateTimeZone.SwitchZone(dateTimeZone, timezoneHours , timezoneMinutes )

Reference:

https://docs.microsoft.com/en-us/powerquery-m/datetimezone-switchzone

Regards,

Jimmy Tao

[Anonymous]

Thanks Jimmy,

I have seen that work around. I have not tried because I used another one.

In any case PowerBI says that it recognise Offset format. I couldn't find in the documentation but it says so when you choose "Change Type" / "Using Locale" / "Date/Time/Timezone" / "Locale: English (United Kingdom)".

And indeed it does work, but when you change it to DateTime it doesn't do the TimeZone conversion (before and after converting it to DateTime):

With the Z format it does change the timezon correctly  (before and after converting it to DateTime):

[v-yuta-msft]

@Anonymous ,

Sorry for late, I have test the four kind of datetimezone format as below, but still couldn't reproduce your issue:

Original text type:

 

After changing to datetimezone format in date type, as you can see the "Offset" in the first row hasn't been truncated on my side. Power bi just don't add the offset to the datetime like "Z format".

 

Regards,

Jimmy Tao

[Anonymous]

Thanks for testing this Jimmy,

Just for the record, there is a mistake in my first post. The expected result is one hour more whne the offset is +00:00 (Because I am in UK in Summer, where it is +01:00).

I tried to replicate the issue and I cound't, thereofore I did a mistake at the beginning or this was solved in the latest update.

Thanks anyway.