cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
croberts21
New Member

How do I capitalize the first letter of each word?

  • 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!

1 ACCEPTED SOLUTION
PhilipTreacy
Super User III
Super User III

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

proper.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

7 REPLIES 7
Icey
Community Support
Community Support

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

employee name.JPG

 

 

 

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
Super User II
Super User II

@croberts21  follow the below steps:

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

step1.PNG

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

step 2.png

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report
croberts21
New Member

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?

@croberts21 

Clicking on Transform Data opens the Power Query editor.

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

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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.

@croberts21 

Try uninstalling PBI Desktop then reinstall.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User III
Super User III

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

proper.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors