cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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 folding or the incremental refresh will be extremely inefficient.  I have tried a dozen differnet ways but I have not found one that works.  Please help.

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

4 REPLIES 4
Community Support
Community Support

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"

2.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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.

View solution in original post

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?

Advocate II
Advocate II

This is marked as solved without having a solution. I switch it back to new and hope for an answer.

 

Please make sure that the question is to be able to change existing or create a new column with a datetime format from an integer (or string) type column with dates in the format of YYYYMMDD, this while retaining the query folding towards the data source

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors