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

    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.