In Excel, Power Query is a business intelligence tool that is used to analyze the data imported from different sources. With the help of power query, we can easily clean, transform and reshape our data as per requirement along the way of analyzing the data. In this example, we will learn about the Text Functions which can be easily used with the help of Power BI for the modification of the data. Given below are the formatting functions that can be used to create and manipulate the text values.

Text Modifications with Power Query

Step 1: Create a Database.

In order to use the text operations, we will be using the following random names. 

Fig 1 – Database

In the above, we can see data is not clean, there is whitespace, some of the text start in lowercase and some are with uppercase. We will use the text formatting option to clean our data after that we will combine them in one column and then we will convert them into uppercase and lowercase.

Step 2: Open Power Query Editor.

In this step, we will open the power query editor window. For this Place Cursor in Table go to the Data tab at the top of the ribbon and then select From Table/Range option. Excel will open Power Query Editor.

 

Once we click on From Table/Range, Excel will automatically open the power query editor. Below, we can see in the power query editor window we have unwanted whitespaces.

 

Step 3: Remove Whitespaces.

In this step, we will remove the whitespace from both columns. For this Select Column then go to the Transform on the top of the ribbon then in the Text Column group choose Format and then select Trim.

Fig 4 – Trim Function

Once we click on Trim option, excel will automatically remove the whitespaces from our column. Similarly, we need to do this for both columns. After we are done, we will get the following output.

 

Note: Whatever steps we do in Power Query Editor, all the steps are getting recorded in the APPLIED STEPS WINDOW. At any point in time, if we want to go back, we can use these steps.

 

Step 4: Combining Columns.

In this step, we will combine the two columns into a single column. For this Select Columns and then go to Transform on the top of the ribbon and then in the Text Columns group choose Merge Columns. 

Fig 7 – Merge Columns

Once we click on Merge Column excel, will open a window asking for the separator to be used while combining the columns. We can use any separator as per requirement (Here, we are using space as a separator while combining first and last name columns). Also, it will ask for the new columns name (Here, we are using Full Name as the new column name).

 

Once we click on OK, the power query will combine both columns into a single column with the name as Full Name.

 

Step 5: Convert Text into Proper Text.

As we can see in the above picture, our data is not in proper form. Some of the letters are started lower and some of the words have uppercase letters in the middle. In this step, we will change our data into proper text. For this Select Column and then go to Transform on the top of the ribbon and then in the Text Columns group choose Format and then select Capitalize Each Word.

 

Once we click on Capitalize Each Word option excel will automatically convert our data into proper text.

 

Step 6: Load Data into Excel Sheet.

Once we are done with our formatting, we can load our data from the power query editor to an excel sheet. For this go to the home tab on the top of the ribbon and then choose the Close & Load option.

 

After we click on the Close & Load option, excel will create a new sheet and load our power query editor data into it.

Note: One of the important features of Power Query Editor is that, if we make any change in our original data, it will update the Power Query Editor’s generated data automatically once we refresh the sheet.