Trim Vs. Clean in Power BI and Power Query

Trim Vs. Clean in Power BI and Power Query

When applying text transformations in Power BI using Power Query; two very important transformations that can help are; Trim and Clean. These two mistakenly often considered to do one thing, but they are different. In this short article and video, I’ll explain their differences through an example.

Video

Sample text

Let’s assume I have a sample text value as below;

some
 samples words    here exists\

The text above has some spaces, tab, end of line characters etc. If I see that in Notepad++ with show all characters, this is how it looks like;

Sample text

Text transformations in Power Query Editor

To do text transformations, You can go to the Transform Data to get the Power Query Editor open.

Transform Data in Power BI Desktop

Trim

Trim is a very commonly used transformation in Power Query. This is used when you want to remove all spaces from the beginning and end of every text value in the field.

You can find Trim under the Transform > Format in the Power Query Editor. Or alternatively right click on the column and under the Transform, select Trim.

Trim transformation in Power Query and Power BI

The result of Trim on the sample text is as below;

Trim removes all the spaces from the beginning and end of the text values

Clean

Clean is not as commonly used as the Trim. Clean removes all the control characters from the text value. This can include end of lines, tabs etc. You can find the Clean transformation in the same place that you found the Trim.

Applying Clean on the sample text will result into below output;

Clean removes all control characters

To see what control characters are, check out this Wikipedia page. In the sample text above the carriage return/line feed, and tabs are removed when used the Clean transformation.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply