topic Re: Format a field to Title Case when using Direct Query in DAX Commands and Tips

Format a field to Title Case when using Direct Query

Ry5 — Tue, 10 May 2022 06:24:47 GMT

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

Re: Format a field to Title Case when using Direct Query

pavanmarri — Tue, 10 May 2022 06:40:51 GMT

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

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

Re: Format a field to Title Case when using Direct Query

Ry5 — Wed, 11 May 2022 00:55:38 GMT

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

Re: Format a field to Title Case when using Direct Query

v-chenwuz-msft — Fri, 13 May 2022 09:55:41 GMT

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.

Re: Format a field to Title Case when using Direct Query

Ry5 — Sun, 15 May 2022 22:42:07 GMT

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.