Downloads
Power Query MTextBeginner

Text.Clean

Removes non-printing control characters, such as line breaks and tabs, from a text value.

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

Returns: The text without control characters, or null when the input is null.

What it does

Memo and description fields often contain line breaks or tabs that came in from a copied PDF, an email or a web page. They look like ordinary text, but they break a report layout and stop matches. Text.Clean removes the non-printing control characters from the value. It does not remove normal spaces or visible punctuation, so pair it with Text.Trim when you also need the spacing tidy.

Syntax and arguments

Example

A controller copies transaction memos out of a PDF bank statement. Some memos wrap onto two lines, which breaks the report. The memo column is cleaned, then trimmed.

let
    Memo = "Payment received" & "#(lf)" & "Invoice 1043",
    Cleaned = Text.Trim(Text.Clean(Memo))
in
    Cleaned

The line break is removed, not turned into a space, so the result is "Payment receivedInvoice 1043". The two words run together. Replace the break with a space first (Text.Replace) when the words must stay apart.

Example: a second, different use

Before a merge, clean an invoice description that contains a tab character copied from a spreadsheet.

Things to watch

In business terms

Memo and description fields are where bank and vendor data arrive messiest. Cleaning them before a report or a reconciliation stops a long memo from breaking a column, and stops a match from failing on an invisible character.

Related functions

All Power Query M functions · View as Markdown · M basics