cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jasonyeung87
Helper IV
Helper IV

converting text to datetime field sometimes change to previous date due to timezone

Hi,

 

I have a column in my table that is a "text" field. The value of the field is a datetime in the format of "2022-11-09T00:00:00Z". I convert this field to a date time field (e.g. in Power query editor, I right click the column header, then go to Change Type --> Date/Time):

 

jasonyeung87_0-1669425788188.png

What's happening is that the resulting date would be the previous date, depending on the timezone. I'm in the Pacific time zone and text like "2022-11-09T00:00:00Z" is converted to 2022-11-08T16:00:00Z" because Pacific is 8 hours behind GMT. 

 

I was wondering how do I convert to a datetime field while ignoring the time in the text.

 

 

2 ACCEPTED SOLUTIONS

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1NNQ1sAwxMLACoyil2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetimezone}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Column1],each DateTimeZone.RemoveZone([Column1]),Replacer.ReplaceValue,{"Column1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1", type date}})
in
    #"Changed Type1"

View solution in original post

Hi @jasonyeung87 !

 

So I changed my timezone on my PC to -12 hours, and this is what I am getting

(excel on the left, PBI on the right)

pi_eye_0-1669793060333.pngpi_eye_1-1669793388241.png

 

If I right click, change type to date/time/timezone it seems to fix the problem.

to recreate, make sure that you select  "replace current" step

pi_eye_2-1669793487086.png

 

This seemed to fix the issue

pi_eye_3-1669793519675.png

 

There are functions that can help -  see  https://learn.microsoft.com/en-us/powerquery-m/datetimezone-functions

You will need to change the type to date/time/timezone before

using these.

EG: 

pi_eye_4-1669793764666.png

^ this will change the time zone by -5 hours

 

You could also try DateTimeZone.ToLocal() 

 

 

HTH 

 

Pi

 

 

 

 

View solution in original post

7 REPLIES 7
lbendlin
Super User
Super User

Since it is in ISO8601 format you should first convert it to DateTimeZone and then in a separate step convert that to Date.

pi_eye
Resolver III
Resolver III

Hi Jason!

 

I was able to recreate this issue by changing my timezone on my laptop. So this is an option - if you change your local desktop to UTC, but if that is not practical, you can also try converting to date/time/timezone 

 

pi_eye_1-1669475492830.png

 

Let me know if it works!

 

Pi

 

 

Hi @pi_eye ,

Is there a way to keep the date the same? For example if the text is "2022-11-09T00:00:00Z", I want the resulting date to be "2022-11-09" instead of "2022-11-08". I don't want it to take the timezone into account when it's converted to the date.

 

Jason

 

Hi @jasonyeung87 !

 

So I changed my timezone on my PC to -12 hours, and this is what I am getting

(excel on the left, PBI on the right)

pi_eye_0-1669793060333.pngpi_eye_1-1669793388241.png

 

If I right click, change type to date/time/timezone it seems to fix the problem.

to recreate, make sure that you select  "replace current" step

pi_eye_2-1669793487086.png

 

This seemed to fix the issue

pi_eye_3-1669793519675.png

 

There are functions that can help -  see  https://learn.microsoft.com/en-us/powerquery-m/datetimezone-functions

You will need to change the type to date/time/timezone before

using these.

EG: 

pi_eye_4-1669793764666.png

^ this will change the time zone by -5 hours

 

You could also try DateTimeZone.ToLocal() 

 

 

HTH 

 

Pi

 

 

 

 

Hi @pi_eye ,

 

thanks for your help. I converted the type to "date/time/timezone" and then looked at what functions are available for the "date/time/timezone" type.

 

I found a function named "DateTimeZone.RemoveZone". I created a new column with this function (e.g. "DateTimeZone.RemoveZone(thefield)") and it worked for me.

 

Jason

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1NNQ1sAwxMLACoyil2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetimezone}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Column1],each DateTimeZone.RemoveZone([Column1]),Replacer.ReplaceValue,{"Column1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1", type date}})
in
    #"Changed Type1"

Hi @lbendlin ,

 

thanks for your help. converting the field to "date/time/timezone" and then using the DateTimeZone.RemoveZone function did the trick.

 

Jason

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.