Forum Discussion

i820017's avatar
i820017
Icon for Resolver II rankResolver II
4 years ago
Solved

Capitalize First and Last Name in a Column

I have a column with the first and last name of each employee.

For example: john doe

I am trying to capitalize the first letter of the first name and the first letter of the last name. 

 

This is the code that I am using (and it I get an error when I use it):

P&P_Analyst_Name1 =
VAR FirstName =
LEFT (
SUBSTITUTE ( 'Procedure Request Form'[P&P_Analyst], ", ", "-" ),
SEARCH ( "-", SUBSTITUTE ( 'Procedure Request Form'[P&P_Analyst], " ", "-" ) ) - 1
)
VAR LastName =
RIGHT (
SUBSTITUTE ( 'Procedure Request Form'[P&P_Analyst], " ", "-" ),
LEN ( SUBSTITUTE ( 'Procedure Request Form'[P&P_Analyst], " ", "-" ) )
- SEARCH ( "-", SUBSTITUTE ( 'Procedure Request Form'[P&P_Analyst], " ", "-" ) )
)
VAR ModifiedFirstName =
UPPER ( LEFT ( FirstName, 1 ) )
& LOWER ( RIGHT ( FirstName, LEN ( FirstName ) - 1 ) )
VAR ModifiedLastName =
UPPER ( LEFT ( LastName, 1 ) )
& LOWER ( RIGHT ( LastName, LEN ( LastName ) - 1 ) )
RETURN
ModifiedFirstName & ", " & ModifiedLastName
 
 
Any idea on how I can fix my code above?

 

 

Need Help
  • PijushRoy's avatar
    PijushRoy
    4 years ago

    Hi i820017 

    Best way to do it in Power Query editor
    Power BI Desktop -> Transform Data -> Power Query Editor opened -> Select The Table -> Right click on column -> Transform -> Capitalize Each Word -> Close and Load

     

    If solve your requirement, please mark this answer as SOLUTION
    If this comment helps you, please LIKE this comment/Kudos

2 Replies

  • truptis's avatar
    truptis
    Icon for Community Champion rankCommunity Champion
    4 years ago

    i820017, Go to Transform Data-> Right click on your field name -> Transform -> Capitalise first word -> Close & Apply Changes

     

    This will fulfill your requirement.

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi i820017 

    Best way to do it in Power Query editor
    Power BI Desktop -> Transform Data -> Power Query Editor opened -> Select The Table -> Right click on column -> Transform -> Capitalize Each Word -> Close and Load

     

    If solve your requirement, please mark this answer as SOLUTION
    If this comment helps you, please LIKE this comment/Kudos