Format a field to Title Case when using Direct Query

[Ry5]

Hello,
I need to set a field to Title Case, which is easily done in Transform Data > Transform Tab > Format > Capitalise Each Word
However I'm getting an error that this can't be done with Direct Query. I can't change this data to Import, it has to be Direct Query. Is there an alternative way in DAX (or however else) to set the field to Title Case?
Thank you

[Ry5]

Thank you
I did find a conversion process here - 

https://stackoverflow.com/questions/63652947/convert-to-proper-case-using-dax
It's an overcomplicated solution but it's there if anyone is unable to set the formatting back in their source data.

[pavanmarri]

In the datasource connection --> Advance options --> Specify the Select query. 

Example : select upper(colname) Col1, lower(colname) Col2 from table1.

[Ry5]

This returns two columns - one with the field in all uppercase, the second with the field in all lowercase
I need the field to be in Title Case, where every word has a capital first letter and the remaining are all lowercase

[v-chenwuz-msft · Accepted solution]

Hi @Ry5 ,

No, Direct Query mode does not support this complex query foldring. The only way to solve the problem is from your database side

The power bi get data form SQL Database is using query foldring to change the query to sql statement and send it to SQLDB, then SQLDB return the data to power bi. 

Query foldring only support simple transform data like sum(), rename().This is for better performance performance and unnecessary errors. For more details you can refer these links.

Query folding - Power Query | Microsoft Docs

Using DirectQuery in Power BI - Power BI | Microsoft Docs

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.