Format Text Using Power Query or Get & Transform

 In this tutorial we will show you these usages of format text: Upper case, Lower case, Capitalize each word and Trim.

You can also browse our Power Query Tutorial Catalog here.

NOTE 1: You can create random excel workbook online here for practice purposes as I did in this tutorial.

NOTE 2: If You use Microsoft Excel Prior to 2016 Version, then You Need to download Power Query Separately.

Previously In Power Query: How to Rename a Column Using Power Query

STEP1. Firstly, select your data and press the shortcut Ctrl + T or by going to Insert > Table then it will turn into Excel Table.

Format Text

STEP2. Now in the second step follow this procedure:

For Excel 2016:Go to Data > Get & Transform > From Table 

For Excel 2013 & 2010: Power Query > Excel Data > From Table

Format Text

STEP3.  After following second step Power Query Editor will be opened. Now we will convert it into Upper case. Follow this:

Go to Add Column > From Text> Format > UPPERCASE

But before following this ensure that your Full Name column is selected.

Format Text

STEP4. Now we will try to convert it into Lower Case. Follow this but ensure that before following this procedure your Full Name column is selected. 

Go to Add Column > From Text> Format > Lowercase

Format Text

STEP5. Now we will show you capitalize each word. Follow this and ensure that you have selected your Uppercase column.

Go to Add Column > From Text> Format > Capitalize each word 

Format Text

STEP6. Now we will show you how to trim the text to rid all of the extra spaces. Select the Full Name column and follow:

Go to Add Column > From Text> Format > Trim

Format Text
Since our workbook doesn’t have spaces, so nothing will happen, it can only be used for those values with extra spaces

We have successfully formatted the text in Power Query in different ways to show you.