User Question:
“I have name and email columns with inconsistent text formatting in my HR database. How can I standardize the text by converting it to uppercase, lowercase, and proper case in Power BI?”
Case transformations in Power Query and Power BI
Assumptions
We will use the following data model:
Table name: EmployeeInfo; Columns: EmployeeID (Whole Number), FullName (Text), Department (Text), Email (Text)
The Basics
- UPPER(): Converts text to all uppercase
- LOWER(): Converts text to all lowercase
- PROPER(): Capitalizes the first letter of each word
These functions work both in Power Query (M language) and DAX, allowing for flexible implementation based on your needs.
Step-by-Step Instructions in Power Query
- Open your Power BI Desktop and load the EmployeeInfo table.
- Go to the “Home” tab and click on “Transform Data” to ultimately open Power Query Editor.
- In Power Query Editor, right-click on the FullName column:
- Go to the “Transform” tab, and click “Format” > “Capitalize Each Word”
- For the Email column:
- Select the column, go to the “Transform” tab, and click “Format” > “lowercase”
- Click “Close & Apply” to apply the changes and return to Power BI Desktop.
Text case setting in DAX
If you are not able to access Power Query, or prefer to use DAX to manipulate your string case, you can create measures for each case conversion:
Upper Case Name = UPPER(SELECTEDVALUE(EmployeeInfo[FullName]))
Lower Case Name = LOWER(SELECTEDVALUE(EmployeeInfo[FullName]))
Proper Case Name = PROPER(SELECTEDVALUE(EmployeeInfo[FullName]))Real-world use cases
Imagine you’re preparing an HR report where employee names need to be displayed in different formats for various purposes:
- UPPERCASE for header displays
- lowercase for email addresses
- Proper Case for formal documents
Additional Tips and Considerations
Working with Multilingual Data
When handling information originally written in different languages, be aware that case conversion functions may behave differently depending on language and locale settings. . Power BI respects culture-specific transformations, but you should verify results when working with non-English datasets. Consider testing your transformations with sample data from each region before deploying to production reports.
Combining Case Functions with Other Text Operations
Case transformations become even more powerful when combined with other text manipulation functions. For instance, you might want to extract first and last names before applying PROPER() formatting, or concatenate multiple fields with consistent casing:
FormattedName = PROPER(TRIM([FirstName])) & " " & UPPER(TRIM([LastName]))This approach ensures clean, standardized output even when source data contains irregular spacing or mixed formatting.
Performance Optimization Strategies
For datasets exceeding 100,000 rows, the timing of your case transformations matters significantly. Applying transformations during the initial data load in Power Query is substantially faster than calculating them dynamically in DAX measures. Power Query transformations are applied once during refresh, while DAX measures recalculate with every user interaction. If your report requires multiple case variations of the same field, create separate columns in Power Query rather than multiple DAX measures.
Preserve identifiers while standardizing text case
Start this text case check with one reproducible example and preserve the current configuration. The goal is to identify the affected layer before applying a broad reset, so change one material factor at a time and repeat the same test.
Isolate the affected layer
Apply Upper, Lower, or Capitalize Each Word in Power Query when case normalization is a stable data-cleaning rule. Preserve the original column until validation and use DAX only when a model-side calculated value is genuinely required.
Names, acronyms, product codes, email addresses, URLs, and case-sensitive keys can be damaged by broad proper-case conversion. Create exceptions or a reference mapping for values such as Microsoft 365, Power BI, and regional abbreviations.
Verify the result safely
Compare distinct counts and join results before and after the change. Trim and clean whitespace separately, test nulls plus non-Latin text, and avoid using display casing as a substitute for a durable key.
On a managed work device or tenant, a missing or reverting control may be intentional policy. Do not bypass it locally; test with non-sensitive content and ask the responsible administrator to confirm the assigned configuration.
When escalation is necessary, provide versions, timestamps, exact error text, and the smallest reproducible example, with confidential values removed. This lets support focus on the correct layer.
Use a before-and-after check that covers the primary task and its nearest dependency. For shared work, verify permissions and ownership as well as the visible result. A fix is incomplete when the original symptom disappears but synchronization, notifications, sorting, recovery, or another required function silently stops.
Keep temporary workarounds clearly labeled and time-limited. Remove permissions, overrides, test accounts, files, and settings that are no longer needed after the supported solution is confirmed, then repeat the standard workflow once more.
Before closing the case, repeat the test with the normal extensions, peripherals, network, and data source restored one at a time. Stop if the symptom returns; that boundary is stronger evidence than a broad reset.
Troubleshooting
- If you are seeing inconsistent results, check for leading/trailing spaces in your original data. Use the TRIM() function in Power Query to remove excess spaces.
- If you have Special Characters: Be aware that PROPER() may not handle special characters or abbreviations correctly (e.g., “o’brien” becomes “O’Brien”). For complex name formatting, consider creating a custom function in Power Query.
- If you run into Performance Issues: If you’re working with large datasets, applying these transformations in Power Query is generally more efficient than using DAX measures. Monitor performance and adjust your approach if needed.
- For Unicode Characters – Ensure your data source encoding supports all characters in your dataset to avoid unexpected results when changing case.