Like the UPPER, LOWER and PROPER functions we can also use Power Query to convert letters cases.
Following is a list of names/words that are written with a combination of upper and lower case letters.
To convert these words into UPPER, LOWER or PROPER cases using Power Query,
Select a cell in the data set > go to the Data tab in the Excel ribbon > From Table/Range > Click OK in the Create Table dialog
See the selected data is loaded into the Power Query Editor
To convert the data into UPPER, LOWER or PROPER CASE, Right-Click on the column header, Transform >
- select ‘lowercase‘ for converting all letters into Small letters,
- select ‘UPPERCASE‘ for converting all letters into Capital letters
- select ‘Capitalize Each Word‘ for Proper case (Starting letter of every word will be converted into Capital Letter and the remaining into small letters)
I have selected UPPERCASE and all letters in the column called Text have been converted into Capital Letters
To create a copy the same data in Lower case, go to the Add Column tab > Format > Select lowercase
Now, we have a copy of the same data with all letters in lower case.
To create another copy the same data, but in Proper case, Select any one of the two columns > in the Add Column tab > Format > Select Capitalize Each Word
By doing that we have created one more copy of the same data but with all words in Proper Case.
Now if you want to rename a column, you can do that either by double-clicking on the column header or using the Right-Click menu. Here, I have renamed the 3 columns as Upper, Lower and Proper.
Right now, I am not removing any of the columns, but you can remove a column or columns using the Remove option available on Right-Click menu.
To load this data into the same Excel worksheet, in the Home tab > Close & Load > Close & Load to…
In the Import Data dialog, > Select Existing worksheet > Select the cell where you want to place the data (Here, I have selected the cell D2) and Click OK
In the transformed table, we have 3 columns called Upper, Lower and Proper which contains words/names in the cases corresponding to column headers.
Whenever you add new data to the Source Table or make some changes to that table, you don’t need to repeat these steps again.
Just right-click on the output table and select Refresh.
Watch my Video on 12 Methods to Clean Data using Power Query