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;

Text transformations in Power Query Editor

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

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.

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

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;

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

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.