Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

DatetimeOffset not converting to DateTime properly

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
Status: Accepted
Comments
v-yuta-msft
Community Support

@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
Not applicable

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)".

 

Power BI DateTimeOffset.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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):

Power BI DateTimeOffset2.pngPower BI DateTimeOffset3.png

 

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

 

Power BI DateTimeOffset Z 1.pngPower BI DateTimeOffset Z 2.png

 

 

 

 

 

 

v-yuta-msft
Community Support

@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:

1.PNG 

 

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".

2.PNG 

 

Regards,

Jimmy Tao

Anonymous
Not applicable

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.

v-yuta-msft
Community Support
Status changed to: Accepted