Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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):
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.
Solved! Go to Solution.
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 @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)
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
This seemed to fix the issue
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:
^ this will change the time zone by -5 hours
You could also try DateTimeZone.ToLocal()
HTH
Pi
Since it is in ISO8601 format you should first convert it to DateTimeZone and then in a separate step convert that to Date.
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
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)
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
This seemed to fix the issue
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:
^ 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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |