Text.Clean Function in Power Query M (4 Easy Examples)

If you want to strip out the invisible control characters that sneak into imported text, like line breaks and tab characters, the Text.Clean function is what you reach for. In this article I’ll show you how it works with a few simple examples.

Table of Contents

Syntax of Text.Clean Function

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

where

text (required, nullable text). The text value you want to strip control characters from. If you pass null, you get null back.

Returns: a nullable text value with every control character removed. If text is null, it returns null.

In plain terms, you hand it some messy text and it gives you back the same text with the invisible junk taken out.

Example 1: Remove a line break from text

Say you have a value with a line break in the middle, written as #(lf) in M.

let
    Source = Text.Clean("Line1#(lf)Line2")
in
    Source

Result: Line1Line2

The line feed is gone and the two pieces sit together as one clean string.

Example 2: Remove several control characters at once

Text.Clean does not stop at line feeds. It also strips tabs (#(tab)) and carriage returns (#(cr)).

let
    Source = Text.Clean("a#(tab)b#(cr)c")
in
    Source

Result: abc

All three control characters are removed in a single pass, leaving just the visible letters.

Example 3: Clean a whole column of messy notes

This is the most common use of Text.Clean, applying it to every row in a column.

Say you have a RawNotes column where each value carries a stray control character from an export.

Here is the starting data:

RawNotes
Order(line break)123
Ship(tab)Fast
Done(carriage return)Now

Now add a new column that runs Text.Clean on each value:

let
    Source = #table({"RawNotes"},{{"Order#(lf)123"},{"Ship#(tab)Fast"},{"Done#(cr)Now"}}),
    Cleaned = Table.AddColumn(Source,"CleanNotes",each Text.Clean([RawNotes]),type text),
    Result = Table.SelectColumns(Cleaned,{"CleanNotes"})
in
    Result

This builds a CleanNotes column with the control characters stripped from each row.

The result keeps only the cleaned values:

CleanNotes
Order123
ShipFast
DoneNow

Every row is now a single clean string you can load or compare without surprises.

Example 4: Spaces are left untouched

Text.Clean only targets control characters, so ordinary spaces survive, including leading and trailing ones.

let
    Source = Text.Clean("  spaced  ")
in
    Source

Result: spaced

Nothing changes here because a space is a printable character, not a control character.

Things to keep in mind with Text.Clean

It does not touch spaces. Leading, trailing, and double spaces all stay (Example 4). To remove those, reach for Text.Trim or Text.Clean followed by it.
Non-breaking spaces survive too. The character #(00A0) from web copy is not a control character, so Text.Clean leaves it in. Replace it with Text.Replace if it is causing trouble.
null returns null, not an empty string. Guard a blank-prone column with Text.Clean([Column] ?? "") if you need text out every time.
It needs text in. A number throws Expression.Error: We cannot convert the value 42 to type Text. Convert first with Text.From.

Common questions about Text.Clean

What is the difference between Text.Clean and Text.Trim?

Text.Clean removes invisible control characters like line breaks and tabs. Text.Trim removes leading and trailing spaces, and Text.TrimEnd clears just the right side. They solve different problems, so messy imports often need more than one.

Does Text.Clean remove spaces between words?

No. It only removes control characters, so every space stays exactly where it was (Example 4).

List of All Power Query Functions

Related Power Query Functions / Articles: