Text.Remove Function in Power Query M (6 Easy Examples)

Text.Remove deletes every occurrence of one or more characters from a text value and returns what is left. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to strip hyphens out of a product code, or clean stray symbols from imported data, this is the function you reach for.

Table of Contents

Syntax of Text.Remove Function

Text.Remove(text as nullable text, removeChars as any) as nullable text

where

text (required, nullable text). The text value to remove characters from.
removeChars (required, any). The character(s) to remove. Pass a single character as a one-character text value, or several characters as a list.

Returns: a text value with all the listed characters removed. If text is null, it returns null.

In plain terms, you give it some text and the characters you want gone, and it hands back the text without them.

Example 1: Remove a single character

Strip the hyphens out of a part code. Pass the character as a plain text value.

let
    Source = Text.Remove("CT-2049-DX","-")
in
    Source

Result: CT2049DX

Every occurrence is removed, not just the first one.

Example 2: Remove a list of characters

To remove more than one character, put them in a list. Here we clean all the punctuation out of a phone number.

let
    Source = Text.Remove("(312) 555-0148",{"(",")"," ","-"})
in
    Source

Result: 3125550148

The parentheses, space, and hyphen are all gone in one call.

Example 3: Remove all digits with a range

Listing all ten digits one by one would be tedious. The .. range syntax builds the list for you.

let
    Source = Text.Remove("maria1984",{"0".."9"})
in
    Source

Result: maria

{"0".."9"} expands to every digit, so any number in the text is removed. {"a".."z"} and {"A".."Z"} work the same way for letters.

Example 4: Keep only the number by removing everything else

You can combine ranges and individual characters in one list. Here we pull the amount out of a label by removing the letters, currency symbol, comma, space, and colon.

let
    Source = Text.Remove("Total: $1,860.25 USD",{"A".."Z","a".."z","$",","," ",":"})
in
    Source

Result: 1860.25

Only the digits and the decimal point survive. You can then convert the result to a number with Number.FromText.

If the value always sits between the same two markers, Text.BetweenDelimiters can pull it out directly.

Example 5: Clean a messy code column

The most common real-world use is cleaning a whole column. Say a Products query came in with stray * and # symbols stuck to the codes.

Here is the starting data:

Item	Code
Lamp	LMP-208*
Desk	DSK-114#
Chair	CHR-77*#

Wrap Text.Remove inside Table.TransformColumns to clean the Code column in place:

let
    Source = Excel.CurrentWorkbook(){[Name="Products"]}[Content],
    Cleaned = Table.TransformColumns(Source,{{"Code",each Text.Remove(_,{"*","#"}),type text}})
in
    Cleaned

The each Text.Remove(_,{"*","#"}) part runs on every value in the column.

The result has clean codes:

Item	Code
Lamp	LMP-208
Desk	DSK-114
Chair	CHR-77

The hyphens stay because they were not in the remove list. Only the characters you name are touched.

Example 6: Matching is case-sensitive

Text.Remove treats uppercase and lowercase as different characters. Here we try to remove a lowercase b from a word that only has an uppercase B.

let
    Source = Text.Remove("Berlin","b")
in
    Source

Result: Berlin

Nothing matches, so the text comes back unchanged. No error is raised when a character is absent.

Things to keep in mind with Text.Remove

There is no comparer argument. You cannot switch to case-insensitive matching the way you can with Text.Contains. To remove both cases, list both: {"b","B"}, or use combined ranges like {"a".."z","A".."Z"}.
Each item in removeChars is a single character. A multi-character string like "USD" in the list does not act as a substring matcher. To remove a whole substring, use Text.Replace with "" as the replacement, or Table.ReplaceValue for a whole column.
Removing every character returns "", not null. Empty text and null behave differently in filters and joins, so check for both when a column can end up fully stripped.
Write ranges low-to-high. Ranges follow Unicode order, so {"0".."9"} works but a reversed range like {"9".."0"} produces an empty list and removes nothing.
It runs locally on large tables. Character-level text operations generally do not fold to a database source, so on big SQL tables the work happens in Power Query rather than on the server. For typical workbook-sized data this is not a concern.

Common questions about Text.Remove

When should I use Text.Select instead of Text.Remove?

When the characters you want to keep are a shorter list than the ones to remove. Text.Select([Code],{"0".."9"}) keeps only the digits, which is simpler than listing everything else for removal.

What is the difference between Text.Remove and Text.RemoveRange?

Text.Remove deletes characters by value, wherever they appear. Text.RemoveRange deletes by position, taking an offset and a count, regardless of what the characters are.

Can Text.Remove strip characters from just the start and end?

No, it removes every occurrence wherever it appears. To clean only the leading and trailing characters, use Text.Trim, which also accepts a list of characters.

List of All Power Query Functions

Related Power Query Functions / Articles: