Date format of STRING changes in completely unrelated dataflow step

[Anonymous]

I have a dataflow that transforms all columns to string and then removes a column. The datetime columns are transformed to string and then, when I remove the column, the format of the date within the string (!), so retroactively, changes it's format. This is madness, but it doesn't stop there: when I make a new query or even a new dataflow, this error comes back. As far as I can tell, the sufficient steps after getting the data are the aforementioned two.

This is how it looks before the change (pay attention to the "posting date" column):

This is after:

This isn't even an English date format, despite me explicitly stating that in the first step. The whole query is this:

let
  Source = Sql.Databases("connection"),
  Navigation = Source{[Name = "database"]}[Data],
  #"Navigation 1" = Navigation{[Schema = "dbo", Item = "table_name"]}[Data],
  Custom = Table.TransformColumnTypes(#"Navigation 1", List.Transform(Table.ColumnNames(#"Navigation 1"), each{_, type text, "en-us"})),
  #"Removed columns" = Table.RemoveColumns(Custom, {"timestamp"})
in
  #"Removed columns"

Does anybody have any idea? Can I get rid of this? (How can a commercial software be this broken...?)

EDIT: You're misunderstanding me. I don't want the string to change, and why should it if I just remove some completely unrelated column? I am just fascinated that the string is changing in a way that is obviously recognizing that the string was once a date. But it isn't anymore.

[Anonymous]

Hi @Anonymous ,

Here are some of my personal thoughts on your question.

1. I noticed that the data type of the “posting date” column shown in your picture is not date/time or date type.

So I think you need to make sure that the column is of date type first. 

2. I also think you can change the updated version of the query with explicit date format:

let
  Source = Sql.Databases("connection"),
  Navigation = Source{[Name = "database"]}[Data],
  #"Navigation 1" = Navigation{[Schema = "dbo", Item = "table_name"]}[Data],
  Custom = Table.TransformColumns(#"Navigation 1", {{"posting date", each DateTime.ToText(_, "yyyy-MM-dd", "en-US"), type text}}),
  CustomText = Table.TransformColumnTypes(Custom, List.Transform(Table.ColumnNames(Custom), each {_, type text})),
  #"Removed columns" = Table.RemoveColumns(CustomText, {"timestamp"})
in
  #"Removed columns"

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FabianSchut]

Instead of converting the date column only to text, can you specify a date formatting for the string too? It may be looking something like this for the date column:

Table.TransformColumns(

    #"PreviousStep",

    {{"DateColumn", each Date.ToText(_, "dd-MMM-yyyy"), type text}}

)