Text.Clean - Text Function

Text.Clean is a Power Query M function that removes control characters from a text value. The function returns a text value with all control characters removed.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Text.Clean( text as nullable text ) as nullable text

Description

The Text.Clean function takes a string of text and removes all control characters. Control characters are special symbols that aren’t usually visible but can affect how the text is displayed or processed. They include things like line feeds, tabs, and carriage returns.

Examples

Let’s dive into some examples to see how this Text.Clean works in practice.

Removing Line Feeds

A line feed, represented by #(lf), is a control character that moves the cursor down to the next line. Let’s say we have a string like this:

"My name is #(lf)Rick."

This would display as:

"My name is
Rick"

If we apply the Text.Clean function to this string, it removes the line feed, resulting in:

"My name is Rick."

Here’s how you would write that in code:

Text.Clean( "My name is #(lf)Rick." ) // Output: "My name is Rick."

Eliminating Tabs

A tab, represented by #(tab), is another control character. It inserts a space that’s equivalent to several regular spaces. For instance, the string:

"My name is #(tab)Rick"

would display as:

 "My name is    Rick"

But if we use Text.Clean on this string, it removes the tab, giving us:

Text.Clean( "My name is #(tab)Rick" ) // Output: "My name is Rick"

Getting Rid of Carriage Returns

A carriage return, represented by #(cr), is a control character that moves the cursor back to the start of the line. For example, the string:

"This text #(tab)shows #(lf)#(cr)control characters."

Would display as:

"This text     shows 
control characters."

With Text.Clean, we get:

// Output: "This text shows control characters."
Text.Clean( "This text #(tab)shows #(lf)#(cr)control characters." )

As you just saw, the Text.Clean function is useful for removing control characters from your text, making it cleaner and easier to read.

Learn more about Text.Clean in the following articles:

Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more

Other functions related to Text.Clean are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/text-clean