Convert Text to Upper, Lower, and Proper Case in Power BI

by

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

  1. Open your Power BI Desktop and load the EmployeeInfo table.
  2. Go to the “Home” tab and click on “Transform Data” to ultimately open Power Query Editor.
  3. In Power Query Editor, right-click on the FullName column:
    • Go to the “Transform” tab, and click “Format” > “Capitalize Each Word”
  4. For the Email column:
    • Select the column, go to the “Transform” tab, and click “Format” > “lowercase”
  1. 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.

Troubleshooting

  1. 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.
  2. 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.
  3. 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.
  4. For Unicode Characters – Ensure your data source encoding supports all characters in your dataset to avoid unexpected results when changing case.