Forum Discussion

Eric's avatar
Eric
Advocate I
6 years ago
Solved

Convert YYYYMMDD String to DateTime w/o breaking query folding

The date field in my source file is stored as a string in YYYYMMDD format.  I need to convert it to a DateTime field in order to do incremental refresh and I need to do that without breaking query fo...
Need Help
  • Eric's avatar
    Eric
    6 years ago

    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.

v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

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"