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