Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX equivalent to the Excel Proper function

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

 

i:0#.f|membership|[email protected]

 

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

Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    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

5 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    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's avatar
      AndyCandy
      Regular Visitor
      3 years ago

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

      • amr0810's avatar
        amr0810
        Frequent Visitor
        1 year ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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

  • amr0810's avatar
    amr0810
    Frequent Visitor
    1 year ago

    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.