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!

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 IV
Resolver IV

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.