convert to proper case using dax

Question

I have a text column and would like to convert it to Proper. Is there a way to do it using DAX only? I dont want to use inbuilt powerbi functions

Proper case is any text that is written with each of the first letters of every word being capitalized. For example, "This Is An Example Of Proper Case." is an example of sentence in proper case. Tip. Proper case should not be confused with Title case, which is most of the words being capitalized.

for example

text_column
apple bat
cab

should change to

Text_column
Apple Bat
Cab

Przemyslaw Remin Przemyslaw Remin · Accepted Answer · 2020-11-19T21:53:56

Here you have general tips on how to simulate PROPER function in DAX. That for sure is possible though there is no native function in DAX for that.

The algorithm for Proper function is:

Get your string to VAR variable.
Convert it to LOWER.
Convert it to list by splitting it with [space] delimiter.
Use REPLACE function to replace the first letter of every string in a list with UPPER.
CONCATENATE the list back to string.

This should be a good starting point:

https://community.powerbi.com/t5/Desktop/DAX-how-split-a-string-by-delimiter-into-a-list-or-array/td-p/559597

Edit.

Here is how to convert text string to proper case with DAX.

I made it the other way. Start with a sample table:

Table = 
    DATATABLE(
        "ID" , INTEGER ,
        "Text" , STRING ,
        {   
            { 1 , "The quick brown fox jumps over the lazy dog" } ,
            { 2 , "Happy Birthday" }
        }
    )

Then add this DAX code as a calculated column:

Text Proper = 
VAR SplitByCharacter = " "
var var_text = 'Table'[Text] 
VAR Table0 =
    ADDCOLUMNS (
        GENERATE (         
            CALCULATETABLE( 'Table', 'Table'[Text] = var_text ), // we shrink table to current row only
            VAR TokenCount =
                PATHLENGTH ( SUBSTITUTE ( 'Table'[Text], SplitByCharacter, "|" ) )
            RETURN
                GENERATESERIES ( 1, TokenCount )
        ),
        //"Word", PATHITEM ( SUBSTITUTE ( 'Table'[Text], SplitByCharacter, "|" ), [Value] ),
        //"cnt", PATHLENGTH ( SUBSTITUTE ( 'Table'[Text], SplitByCharacter, "|" ) ),
        "Proper", 
            var word = PATHITEM ( SUBSTITUTE ( 'Table'[Text], SplitByCharacter, "|" ), [Value] )
            var word_Propper = UPPER( LEFT( word, 1 ) ) & LOWER( RIGHT( word, LEN( word) - 1 ) )
            return
                word_Propper
    )
RETURN 
    CONCATENATEX( Table0, [Proper], " " )

I cooked this code with inspiration of the article by Phil Seamark:

https://dax.tips/2019/07/05/dax-pivot-text-into-a-list-of-words/

convert to proper case using dax

2 Answers