Forum Discussion
Helper IUnusual Behaviour when handling GMT time stamps
Helper IThanks Collinsg
That makes a lot of sense and I appreciate the background for why I get the two values showing differently between the two views. Howewver it shoudl not be showing as a datetime at all as I had declared it a date in the column.
Do you happen to know why the explicit assignment of date type in the column. Does not actually convert the value? You can see the column datatype is date but showing a datetime format?
I verified it by using Table.Schema
Solution SageGood day MattSB,
I haven't got an answer "from first principles" but perhaps a clue. If I add a column to a table,
= Table.AddColumn(tbl, "Custom", each 100, type text)
what I notice is the column header shows ABC but the values are right-aligned. The right-alignment suggests they are stored as numbers despite the "type text" (I verified they are stored as numbers by then adding a custom column which added 1 to my column - no error was thrown by the math operation, an error would have been thrown if the 100s had been stored as text).
This is similar to what you are seeing.
When I then explicitly change the column type,
= Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}})
the values become left aligned. This suggests they are now stored as text (again I was able to verify this by adding a column, this time with a text operation).
It seems, then, as if ", type x" is not as strong as TransformColumnTypes - it's as if it only changes the icon at the top of the column. Maybe the ", type x" is taken as a suggestion but Power Query looks at the column values and makes its own assessment based on the values, overriding ", type x" if it makes a different assessment.
It would be very interesting to know the answer.