Updated on

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


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


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.


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

But 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, “My name is #(tab)Rick” would display as:

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

But again, 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, “This text #(tab)shows #(lf)#(cr)control characters.” would display as:

"This text     shows 
control characters."

But with Text.Clean, we get: “This text shows control characters.” The code for this would be:

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

In summary, the Text.Clean function is a handy tool in the Power Query M language for removing control characters from your text, making it cleaner and easier to read or process.

Learn more about Text.Clean in the following articles:

Other functions related to Text.Clean are:

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