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?
Solved! Go to Solution.
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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@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"
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?
Clicking on Transform Data opens the Power Query editor.
If it's crashing try restarting PBI Desktop and/or the PC.
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.
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps