Clean text (Text.Clean function) in Power Query removes control and non-printable characters.
This is useful for cleaning text data.
For example, if your source data has had things like line breaks added, using text.clean will allow you to remove these in one step.
Cleaning text in Power Query is different from other text functions such as trim text, which removes whitespace and leading/trailing characters.
How to use clean text in Power Query
To clean text in Power Query, right-click on the column you want to clean, then navigate to ‘Transform’, and then select ‘Clean’.
See the image below.
Here is an example of some data that we can clean in Power Query:
Once we have followed the above steps and cleaned the text, here is what the results are in Power Query:
The line breaks have been removed (cleaned) but trailing and leading whitespace remains. To remove this, you need to use the Text.Trim function instead.