Forum Discussion
Convert YYYYMMDD String to DateTime w/o breaking query folding
Thank you for the reply. Yes, I have already tried the Table.TransformColumnTypes approach along with Table.AddColumn. Both break query folding because I'm changing a text field to a date field. Right now, I've switched back to odbc.query and I am pushing as much into the SELECT query as possible to leverage the service on the initial step. It's not query folding but it may well improve performance, we'll see.
Hi Eric ,
Did you want to change the data type without adding a new step? It is not possible in power query as I know.
I need to do that without breaking query folding
BTW, please check this way.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDQ0MjZUitWBcAwMDZA4RiBOLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t]),
#"Duplicated Column" = Table.DuplicateColumn(Source, "date", "date - Copy"),
#"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"date - Copy", type date}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"date - Copy", type datetime}})
in
#"Changed Type1"
Thank you for the reply. Yes, I have already tried the Table.TransformColumnTypes approach along with Table.AddColumn. Both break query folding because I'm changing a text field to a date field. Right now, I've switched back to odbc.query and I am pushing as much into the SELECT query as possible to leverage the service on the initial step. It's not query folding but it may well improve performance, we'll see.
Please do not mark as solved when it it is clearly not. Its very frustrating searching for what is clearly a common problem. The issue is how to convert text to date without breaking query folding not just the simple text to date conversion. If anybody has truly solved this please post the solution.
I'm also looking for a solution. All the dates in my SQL database are stored as text, not as numbers.
And I can't convert them to date without breaking the query folding (which means, for example, that I can't use those fields for incremental refresh).I wonder if there is a way to convert without breaking query folding. There should be a way for PQ to convert in natural SQL language, since there is a CAST() function in SQL, no?
