Open In App

Text Modifications with Power Query in Excel

Improve
Improve
Like Article
Like
Save
Share
Report

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.

Function Name

Description

Trim

It is used to trim(remove) the whitespace from both ends of the text.

Clean

It removes the non-printable characters from the text and returns it.

Merge Columns

It is used to combine two or more columns into a single column using a separator.

Lowercase

It is used to make text lowercase.

Uppercase

It is used to make text uppercase.

Add Prefix

It is used to add prefixes to a cell or set of cells.

Add Suffix

It is used to add suffixes to a cell or set of cells.

Capitalize Each Word

It is used to change a text into proper text.

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. 

Creating-database

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.

opening-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.

power-query-editor-opens

 

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.

Using-trim-function.

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.

After trim, 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.

applied-steps-window

 

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. 

combining-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).

merging-columns

 

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

full-name-merged-column

 

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.

using-capitalize-each-word.

 

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

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.

using-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.

new-sheet-for-dataset

 



Last Updated : 27 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads