DAX equivalent to the Excel Proper function

[Anonymous]

My input data, from an import from SharePoint, contains the following

i:0#.f|membership|jason.king@prisoner.com

I know how to get the jason king part of the person's name (Mid/Left/Right etc) but I'm looking to be able to capitalise the first letter of each part of the name, ending up with Jason King. 

There appears to be no DAX equivalent of excel's Proper, or am i wrong ?

Regards

Fred

[amr0810]

Not sure which version of Power BI was being used at the time. Lastest version November 2024, and I am sure previous one, in Power Query mode there is a transform text format function, where you can transform a text column to multiple case format, one of the is "Capitilize Each Word". Others are UPPERCASE, lowercase.

[Samarth_18 · Accepted solution]

Hi @Anonymous ,

In the Query Editor, go to the Transform tab, select Format, and select Capitalize Each Word.

And if you want use it in your measure then you can use below code:-

Column =
VAR _lastName =
    MID (
        [Your_string],
        SEARCH ( " ", [Your_string], 1, 1 ),
        LEN ( [Your_string] ) - 1
    )
VAR _firstName =
    MID ( [Your_string], 1, SEARCH ( " ", [Your_string], 1, 1 ) - 1 )
RETURN
    UPPER ( LEFT ( _firstName, 1 ) )
        & RIGHT ( _firstName, LEN ( _firstName ) - 1 ) & " "
        & UPPER ( LEFT ( TRIM ( _lastName ), 1 ) )
        & RIGHT ( TRIM ( _lastName ), LEN ( TRIM ( _lastName ) ) - 1 )

Thanks,

Samarth

[AndyCandy]

Appreciate the solution, but its kinda nuts that there isnt a built in formula for this like in Excel.

[amr0810]

In Power Query, there isa column transform function to set proper casing in a text column, includes UPPERCASE, lowercase, Capitalize Each Word

[Anonymous]

Thanks @Samarth_18 that pointed me in the right direction as I found that some of the entries had either no second name or were entirely missing, but your code gave me the right starting point

regards

Fred