Dax measure needed

[E12345]

Hi! I need help formatting a text column. It seems so easy to do it in SQL using string functions, but I need help doing it using DAX:

I have a column that has two problems - each value is a list of names, but they look bad vecause there is no space after each comma, and the names are not always capitalized.

Example of my column with values that does not look neat and needs to be fixed:

"My Column"
asmith,jnorth,wjones
aLee,mjonson,rwillis,KBronson
Jjones,frowen

I need the values to look like this (single space after each comma and first two letters of each name capitalized while other letters are in lower case):

"My Column"
ASmith, JNorth, WJones
ALee, MJonson, RWillis, KBronson
JJones, FRowen

Thank You! It has to be a neasure that I need to place into a "Resource" field of a gantt chart on a Power BI project. 

[danextian]

Hi @E12345 

Add this custom column in the query editor:

let
    // Split the text in MyColumn using commas as the delimiter
    SplitText = Text.Split([MyColumn], ","),

    // Transform each part by uppercasing the first two characters and lowercasing the rest
    UppercasedFirstTwoChars = 
    List.Transform(SplitText, each Text.Upper(Text.Start(_,2)) & Text.Lower(Text.End(_, Text.Length(_)-2))),

    // Recombine the transformed parts into a single text, joined by commas
    Recombined = Text.Combine(UppercasedFirstTwoChars, ", ")
in 
    // Output the recombined text
    Recombined

[E12345]

Thank you so much, but you are using M language, and I need a DAX measure to put in Power BI Desktop. We use live connection so I do not have access to Power Query/M language. 

[v-jtian-msft]

Hello,danextian ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hi,@E12345 .I am glad to help you.
I note that danextian has already provided a solution case for dax code.
However, since you are using live connections as your data connection mode.There are some DAX functions and capabilities that may be limited.Live Connections mode is primarily used for connecting to existing Power BI datasets or Analysis Services models, rather than importing data directly. As a result, some data manipulations and calculations may not be as flexible as in Import mode.
This is related to the version of your model.
You can try testing this measure in Power BI Desktop using Live Connections mode to see if it works properly.
As you mentioned, in Live Connections mode, users usually don't have access to modify the data model. Therefore, moving some of the calculations to the data source for preprocessing is also a better solution. This ensures that when you use Live Connections in Power BI, the data has been processed the way it needs to be.
Provided you have the authority to modify that AS model.
This is a limitation of the Live connections connection model.
Solved: Re: Live connection limitations - Microsoft Fabric Community

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[E12345]

Thank you for the suggestion. I do npt have access to the source data/data model, so I only rely on Dax measures at the Power BI Desktop side. 

[danextian · Accepted solution]

I realized you wanted DAX so here they are

CapitalizedFirstTwoCharacters CalcColumn = 
VAR CountOfComma =
    LEN ( 'Table'[MyColumn] ) - LEN ( SUBSTITUTE ( 'Table'[MyColumn], ",", "" ) )
VAR Numbers =
    GENERATESERIES ( 1, 2, 1 )
VAR Substituted =
    SUBSTITUTE ( 'Table'[MyColumn], ",", "|" )
VAR _Names =
    ADDCOLUMNS (
        Numbers,
        "@name",
            VAR _item =
                PATHITEM ( Substituted, [Value] )
            VAR _itemLength =
                LEN ( _item )
            RETURN
                UPPER ( LEFT ( _item, 2 ) )
                    & LOWER ( RIGHT ( _item, _itemLength - 2 ) )
    )
RETURN
    CONCATENATEX ( _Names, [@name], ", " )

CapitalizedFirstTwoCharacters Measure = 
VAR _Value = MAX ( 'Table'[MyColumn] )
VAR CountOfComma =
    LEN ( _Value ) - LEN ( SUBSTITUTE (_Value, ",", "" ) )
VAR Numbers =
    GENERATESERIES ( 1, CountOfComma, 1 )
VAR Substituted =
    SUBSTITUTE (_Value, ",", "|" )
VAR _Names =
    ADDCOLUMNS (
        Numbers,
        "@name",
            VAR _item =
                PATHITEM ( Substituted, [Value] )
            VAR _itemLength =
                LEN ( _item )
            RETURN
                UPPER ( LEFT ( _item, 2 ) )
                    & LOWER ( RIGHT ( _item, _itemLength - 2 ) )
    )
RETURN
    CONCATENATEX ( _Names, [@name], ", " )

[E12345]

Thank you so much! You are so close, but this function drops one of the names on the list as you can see from your data sample (there is always one name missing if there is more than one name on the line). Can this function be updated? TY!