topic Re: Date format of STRING changes in completely unrelated dataflow step in Dataflow

Date format of STRING changes in completely unrelated dataflow step

Anonymous — Fri, 15 Nov 2024 08:21:52 GMT

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.

Re: Date format of STRING changes in completely unrelated dataflow step

FabianSchut — Thu, 14 Nov 2024 16:33:21 GMT

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",

)

Re: Date format of STRING changes in completely unrelated dataflow step

Anonymous — Fri, 15 Nov 2024 01:38:27 GMT

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.