How to capitalize the first letter of each word in DAX

[Anonymous]

Text	Calculated Column
how_are_you	How Are You
welcome_to_power_bi	Welcome To Power Bi

Hi Guys,

I am new to power bi. I need to create the calculated column as shown in the above table. Thanks in Advance 

@Anonymous @PowerBI 

[Greg_Deckler · Accepted solution]

@Anonymous Here is a complete DAX solution that capitalizes each word:

Column = 
    VAR __Separator = "_"
    VAR __SearchText = 'Capitalize'[Text]
    VAR __Len = LEN(__SearchText)
    VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                GENERATESERIES(1,__Count,1),
                "__Word",
                    VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1))
                    VAR __Start =
                        SWITCH(TRUE(),
                            __Count = 1,1,
                            [Value] = 1,1,
                            FIND("|",__Text)+1
                        )
                    VAR __End = 
                        SWITCH(TRUE(),
                            __Count = 1,__Len,
                            [Value] = 1,FIND("|",__Text) - 1,
                            [Value] = __Count,__Len,
                            FIND(__Separator,__Text,__Start)-1
                        )
                    VAR __Word = MID(__Text,__Start,__End - __Start + 1)
                RETURN __Word
            ),
            "__Replace", UPPER(LEFT([__Word],1)) & RIGHT([__Word],LEN([__Word])-1)
        )
RETURN
    CONCATENATEX(__Table,[__Replace]," ",[Value])

[Anonymous]

Hi @Greg_Deckler ,

The above code which you given is working fine for import mode. But it's not working for the Direct Query Mode. My source Data is Microsoft SQL Server. Thanks 

[Greg_Deckler]

@Anonymous Do you know what function is failing in DirectQuery mode? I just created this Measure version, there is only a single change. I tested this in Direct Query mode against the AdventureWorks database and it worked just fine.

Measure = 
    VAR __Separator = "_"
    VAR __SearchText = MAX('Capitalize'[Text])
    VAR __Len = LEN(__SearchText)
    VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                GENERATESERIES(1,__Count,1),
                "__Word",
                    VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1))
                    VAR __Start =
                        SWITCH(TRUE(),
                            __Count = 1,1,
                            [Value] = 1,1,
                            FIND("|",__Text)+1
                        )
                    VAR __End = 
                        SWITCH(TRUE(),
                            __Count = 1,__Len,
                            [Value] = 1,FIND("|",__Text) - 1,
                            [Value] = __Count,__Len,
                            FIND(__Separator,__Text,__Start)-1
                        )
                    VAR __Word = MID(__Text,__Start,__End - __Start + 1)
                RETURN __Word
            ),
            "__Replace", UPPER(LEFT([__Word],1)) & RIGHT([__Word],LEN([__Word])-1)
        )
RETURN
    CONCATENATEX(__Table,[__Replace]," ",[Value])

[Anonymous]

What I did means. I added a query to the measure and then added the measure to the table chart. It shows me an error which I was uploaded as a picture.

Here I added a query to the calculated column. It could not identify some functions. These two issues I was facing.

@Greg_Deckler 

[Greg_Deckler]

@Anonymous Right, as a calculated column in a Direct Query model, half the DAX functions aren't going to work. I don't understand the issue with the measure as I tested this specifically against a Direct Query model. Please paste the full text of your DAX measure and I can take a look. Seems like you have something incorrect in your RIGHT function call.

[Anonymous]

Measure = 
    VAR __Separator = "_"
    VAR __SearchText = MAX('ValuationProcedureData'[AssetStatus])
    VAR __Len = LEN(__SearchText)
    VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                GENERATESERIES(1,__Count,1),
                "__Word",
                    VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1))
                    VAR __Start =
                        SWITCH(TRUE(),
                            __Count = 1,1,
                            [Value] = 1,1,
                            FIND("|",__Text)+1
                        )
                    VAR __End = 
                        SWITCH(TRUE(),
                            __Count = 1,__Len,
                            [Value] = 1,FIND("|",__Text) - 1,
                            [Value] = __Count,__Len,
                            FIND(__Separator,__Text,__Start)-1
                        )
                    VAR __Word = MID(__Text,__Start,__End - __Start + 1)
                RETURN __Word
            ),
            "__Replace", UPPER(LEFT([__Word],1)) & RIGHT([__Word],LEN([__Word])-1)
        )
RETURN
    CONCATENATEX(__Table,[__Replace]," ",[Value])

@Greg_Deckler 

[Greg_Deckler]

@Anonymous I am guessing that it is a boundary case where there is only a single word. Going to check.

[Greg_Deckler]

@Anonymous Yep, that was it, specifically a single letter followed by an _ Try replacing the "__Replace" line of your code with this:

            "__Replace", 
                    VAR __Rest = LEN([__Word])-1
                RETURN
                    IF(__Rest<=1,UPPER(LEFT([__Word],1)),UPPER(LEFT([__Word],1)) & RIGHT([__Word],LEN([__Word])-1))

[Anya_T]

Hi,

In case you want to use DAX then try this

Column = UPPER(SUBSTITUTE(Sheet2[Text], "_", " ") )

[danielwelch]

Hi,

in power query editor > Transform Tab > Format.  Then select uppercase.

Right click on rown and select remove values - remove _ and replace with " " .

[selimovd]

Hey @Anonymous ,

that's difficult in DAX but easy in M/Power Query.

Just go to Power Query, replace underscore with a space and use the "Capitalize Each Word" function:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic