Forum Discussion
Helper IUnusual Behaviour when handling GMT time stamps
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.
Helper IThats very interesting and got me thinking , as I know that Gen 2 Dataflows allows you explicitly declare in column data types in the "Custom columns" GUI.
Making the same query in fabric reveals two interesting differnences
The appended ", type x" pattern is not used instead its
Table.TransformColumnTypes(Table.AddColumn(Source, "ColumnName", each "DO SOMETHING HERE" ), {{"ColumnName", type x }})Additonally the Record and the visual preview table shows the same value so it looks like the rounding for preview is different in the new dataflows.
The key takeaway is your insight that appending ", type x" is clearly not best practice and we should really wrap with TransformColumnTypes() instead.