Convert text or value to date that supports Query Folding?

[edhans]

Does anyone know if it is possible to convert any type of date-looking data into a date that will be supported by query folding for SQL Server?

Prior to the date conversion, I can get the data in just about any format you want that is suported by folding. For example:

• 201701 as a number (converts to Jan 1 2017 but breaks folding)
• 20170101 as a number
• 201701 as text
• 20170101 as text
• 2017-1 as text
• 2017-1-1 as text

I could pretty much do whatever necessary, but cannot figure out a way to convert any of those to a date that doesn't break folding.  It looks like the way to do this in native SQL is with the CONVERT() function (and there may be other ways) but am I missing something to get Power Query to do this?

In some of my models I can hold off converting to date very very late in the query process so the performance impact isn't huge, but in other scenarios, I need a date pretty early.

[DeepEureka]

Do you have acces to the source database?

One option could be to create a view in the database that performs the SQL convert operation, and then load on PowerQuery this view, not the original table. This should preserve the query folding.

I agree it is very strange that date conversions prevent the query folding to happen.

Regards,

David

[v-chuncz-msft]

@edhans,

You may try using function Date.From.

[edhans]

No. Date.From() breaks folding as far as my tests have gone.

[edhans]

The weird thing is after I have text in a date looking format, as in the OP, if I just add ", type date" to that line, PQ shows it as a date in the header, folding is not broken, but it isn't a date in the contents column. Still text, it just shifted to left justification. Seems like a bug. It should either cause an error, convert to a date, or show in the column header it is still text.