DAX equivalent to the Excel Proper function

[MitieFred]

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

[Samarth_18 · Accepted solution]

Hi @MitieFred ,

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.

[MitieFred]

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