How do I capitalize the first letter of each word?

[croberts21]

• PBI Desktop v2.88.1682 (64 bit free version) 
• PC: Windows 10 Pro (x64) with 32GB of RAM and HDD
• Dell OptiPlex 5070 with Intel i7-9700 CPU @ 3GHz, 8 cores
• Display is 1920x1080 @ 32Hz, 32 bit color, it uses an Intel UHD Graphics 630 graphics chip with 1GB RAM
• I use an ODBC driver to connect to a Postgresql database server.
• I'm new to PBI so my apologies if I get a term incorrect.

I'd like to capitalize the first letter of each part of an employee's name, with the rest of the letters in lowercase. The data could be entered in any case. We have 2 fields, one for firstname, one for lastname. But the output will be "Lastname, Firstname". The output is a calculated field. Examples: 

"SMITH, BOB" becomes "Smith, Bob"

"green, john" becomes "Green, John"

I searched through PBI help and did not find any functions like ucfirst() or propercase(). How would I do this? 

If PBI doesn't have this function, will it be added in the future?

Thank you!

[Icey]

Hi @croberts21 ,

You can also try to create a calculated column like so:

Modified Employee Name = 
VAR FirstName =
    LEFT (
        SUBSTITUTE ( [Employee Name], ", ", "-" ),
        SEARCH ( "-", SUBSTITUTE ( [Employee Name], ", ", "-" ) ) - 1
    )
VAR LastName =
    RIGHT (
        SUBSTITUTE ( [Employee Name], ", ", "-" ),
        LEN ( SUBSTITUTE ( [Employee Name], ", ", "-" ) )
            - SEARCH ( "-", SUBSTITUTE ( [Employee Name], ", ", "-" ) )
    )
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

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tahreem24]

@croberts21  follow the below steps:

Step 1: Open Transform Query under Home menu. And click on Transform Data.

Step 2: Then it will open Power Query Editor. right click on your Employee Column name --> then choose Transform then select "Capitalize each word"

[croberts21]

The Microsoft docs show how to edit a query but I do not have that in PBI Desktop v2.88. Is the query editor a separately installed product? 

I do not see Edit Query in any mode: Report mode, Data mode, Model mode. I do not see it on any tab bar. And we are having trouble doing transform on this table, PBI just hangs when I choose Transform Data, which is a separate issue.

In my version of PBI Desktop, how do I find the Edit Query button?

[PhilipTreacy]

@croberts21 

Clicking on Transform Data opens the Power Query editor.

If it's crashing try restarting PBI Desktop and/or the PC.

Regards

Phil

[croberts21]

Oh dear. Well, going into Transform Data for this table hangs PBI, although I can escape out of it. I will have to get this hanging problem fixed first. Thank you.

[PhilipTreacy]

@croberts21 

Try uninstalling PBI Desktop then reinstall.

Regards

Phil

[PhilipTreacy · Accepted solution]

Hi @croberts21 

You can do this in Power Query using the function Text.Proper or by right clickng the colum header and selecting Transform -> Capitalize Each Word

Regards

Phil