Forum Discussion
Community ChampionConvert text or value to date that supports Query Folding?
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.
4 Replies
v-chuncz-msftCommunity Support
- 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.
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
