Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Proud to be a Super User!
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.
@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.
Regards
Phil
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.
Try uninstalling PBI Desktop then reinstall.
Regards
Phil
Proud to be a Super User!
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |