Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingDo 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
No. Date.From() breaks folding as far as my tests have gone.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |