cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User II
Super User II

Convert 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Convert text or value to date that supports Query Folding?

@edhans,

 

You may try using function Date.From.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User II
Super User II

Re: Convert text or value to date that supports Query Folding?

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Super User II
Super User II

Re: Convert text or value to date that supports Query Folding?

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors