Forum Discussion
Date value changes when transforming column type
I cannot figure out why changing a "DateTimeZone" column to type "Date" causes the date value to change.
First I convert my UTC DateTime to IST DateTimeZone
= Table.AddColumn(#"table from database", "IST Time", each DateTimeZone.SwitchZone(DateTime.AddZone([CreatedOn],0), 5.5), type datetimezone)
Then I convert to Date format
= Table.TransformColumnTypes(#"Duplicated Column",{{"IST Time", type date}})
I have outlined the dates which have been 'converted' incorrectly (Showing a copy column of Step 1 for demonstration)
I can't figure out what's going wrong. My only thought is my machines system time zone offset (+11) could be impacting it. But the threshold for when Power Bi gets it wrong doesn't seem to add up.
Thanks for the help
Ok... I tried using Date.From() but noticed in the doco it says:
- datetimezone: The date component of the local datetime equivalent of value.
..... So, it uses "local" datetime equivalent. Unsurprisingly, it gave me the same "error" as using Table.TransformColumnTypes
It didn't take long on the doco page, though, to find the answer: DateTimeZone.RemoveZone()
(then change column to "Date" format 🙂)
= Table.AddColumn(#"Changed Type", "DateTimeFrom", each DateTimeZone.RemoveZone([IST Time2]), type datetime)
1 Reply
Ok... I tried using Date.From() but noticed in the doco it says:
- datetimezone: The date component of the local datetime equivalent of value.
..... So, it uses "local" datetime equivalent. Unsurprisingly, it gave me the same "error" as using Table.TransformColumnTypes
It didn't take long on the doco page, though, to find the answer: DateTimeZone.RemoveZone()
(then change column to "Date" format 🙂)
= Table.AddColumn(#"Changed Type", "DateTimeFrom", each DateTimeZone.RemoveZone([IST Time2]), type datetime)