How to Remove Special Characters in Power BI

Struggling with strange symbols making a mess of your Power BI reports? You're far from alone. Text data imported from websites, CRMs, or user-input fields often arrives full of unwanted characters - things like #, $, ™, or even invisible line breaks. These aren't just ugly, they can seriously sabotage your analysis. This guide will walk you through several methods in Power Query, from simple point-and-click fixes to powerful formulas, to get your text data clean and your reports trustworthy.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Do Special Characters Cause So Much Trouble?

On the surface, a stray symbol might seem harmless. But in the world of data analysis, these characters can cause a cascade of problems that lead to inaccurate insights and broken dashboards. Here’s why you need to get rid of them:

• Data Type Errors: Power BI is smart, but it's not a mind reader. When it sees a value like "$1,234.56", it classifies it as text, not a number. You can’t sum, average, or perform any mathematical operations on a column full of text. Removing the dollar sign allows you to correctly convert the column to a decimal or whole number type.
• Failed Relationships: Data models rely on clean, matching values to create relationships between tables. If your sales table has "Product ABC®" but your product lookup table just has "Product ABC", Power BI sees them as two entirely different things. That relationship will fail, and your slicers and filters won't work correctly.
• Incorrect Calculations: Functions that rely on matching text, like COUNT or SUMX logic in DAX, will fail to aggregate your data properly. You might end up with two separate rows for "T-Shirt" and "T-Shirt #Blue", leading you to believe you have multiple products when you really just have messy data.
• Unprofessional Reports: Let's be honest - charts, tables, and slicers filled with random characters like &trade, or @ look sloppy. Clean data leads to clean, professional-looking visuals that your stakeholders can easily understand and trust.

Method 1: The Quick Fix with "Replace Values"

The simplest way to remove one or two known special characters is with Power Query’s built-in "Replace Values" feature. If you have a column of phone numbers with hyphens you want to remove, or product names with hashtags, this is your go-to starting point. It requires no code and you can see the results instantly.

Step-by-Step Instructions

1. From the main Power BI Desktop window, click on Transform data in the Home ribbon. This will open the Power Query Editor.
2. In the editor, find and select the column that contains the special characters you want to remove.
3. Navigate to the Transform tab in the Power Query ribbon at the top of the screen.
4. Click the Replace Values button.
5. A new dialog box will pop up. In the Value To Find field, type the special character you want to eliminate (e.g., #).
6. To remove the character completely, leave the Replace With field blank. If you want to replace it with something else (like a space), you can enter that here.
7. Click OK. You'll see the character disappear from your column immediately.

You can repeat this process for any other characters you need to remove. Each time you do, Power Query will add a new "Replaced Value" step in the Applied Steps pane on the right. This creates a repeatable cleaning recipe for your data refresh.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

When to Use This Method

• It's ideal for removing a handful of specific, visible characters.
• Perfect for beginners as it's entirely UI-driven with no formulas to write.

What to Watch Out For

• This method becomes inefficient if you need to remove many different characters. Creating 15 separate "Replaced Value" steps will make your query slower and harder to manage.

Method 2: Handling a Target List with the M Formula Text.Remove

When "Replace Values" feels too repetitive, you can lean on M, Power Query's formula language. The Text.Remove function lets you specify a list of characters to scrub from your text in a single step. This is much more efficient than chaining together multiple "Replace Value" actions.

You'll typically use this function when creating a new custom column.

Step-by-Step Instructions

1. In the Power Query Editor, click on the Add Column tab in the ribbon.
2. Click the Custom Column button.
3. In the Custom Column dialog, give your new column a name, such as "ProductName_Cleaned".
4. In the formula box, type the following M code, making sure to replace [YourColumnName] with the actual name of your messy column:

Text.Remove([YourColumnName], {"#", "$", "%", "&", "!", "*"})

This formula tells Power Query to look in each row of YourColumnName and remove any character found in the provided list.

Pro-Tip: For a cleaner formula, you can also group all the characters into a single text string:

Text.Remove([YourColumnName], "#$%&! *")

1. Check for the "No syntax errors have been detected." message, then click OK.

A new, cleaned column will appear at the right end of your table. From here, you can right-click the original column and select Remove, and then rename your new column to take its place.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 3: The Ultimate Solution with Text.Select

Sometimes, your data is so messy that you don't even know every possible special character lurking inside. You might encounter different currency symbols, various accent marks, or miscellaneous technical symbols. In these cases, it's often easier to define what you want to keep rather than what you want to remove. The Text.Select function is masterful at this.

With it, you can tell Power Query to keep only alphanumeric characters (A-Z, 0-9) and perhaps a few others like spaces, and to discard everything else.

Step-by-Step Instructions

1. Once again, go to the Add Column tab and click Custom Column.
2. Name your new column something clear, like "Description_Alphanumeric".
3. In the formula area, enter a formula that specifies character ranges. To keep all uppercase letters, lowercase letters, and numbers, use this:

Text.Select([YourColumnName], {"a".."z", "A".."Z", "0".."9"})

This versatile approach automatically removes emojis, currency symbols, accented characters, and anything else not in the English alphabet or numeric system.

How to Keep Spaces, Too

You'll quickly notice the formula above also removes spaces between words. To keep them, simply add a space " " to your list of approved characters:

Text.Select([YourColumnName], {"a".."z", "A".."Z", "0".."9", " "})

1. Click OK, and your new column will contain only the characters you explicitly allowed.

Why This Method is So Powerful

• It's a defensive approach that cleans data without you needing to create an exhaustive list of every possible bad character.
• It future-proofs your solution, as any new or unexpected special characters introduced into your source data will be removed automatically.

Bonus Tips for Squeaky Clean Data

Removing special characters is a huge step, but a couple of other functions - Text.Clean and Text.Trim - often go hand-in-hand to complete the job.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Using Text.Clean and Text.Trim

These two functions tackle the "invisible" problems in your text data:

• Text.Trim: Removes all leading and trailing whitespace. " Example Company " becomes "Example Company". This is essential for getting relationships and lookups to work correctly.
• Text.Clean: Removes any non-printable characters from your text. This commonly includes line breaks and tabs that may have been copied over from a website or another source. These characters are often completely invisible in the Power Query grid but can prevent data type conversion and cause other errors.

Luckily, you don't need a custom formula for these. Just right-click on your column header, go to Transform, and you'll see options for both Trim and Clean.

A Complete Data Cleaning Workflow

For truly robust cleaning, you can combine these methods in a logical order within your Applied Steps:

1. First, use Trim and Clean on your column.
2. Next, use your preferred method (Replace Values or a custom column with Text.Select and Text.Remove) to handle the visible special characters.
3. After all text transformations are done, if the column contains numerical or date information, change its Data Type (e.g., to Whole Number, Decimal Number, or Date).

This layered approach ensures that both visible and invisible issues are resolved before you load the data into your model.

Final Thoughts

Cleaning special characters out of your data in Power BI doesn't need to be a major headache. Whether you use the simple "Replace Values" for a quick edit or an advanced Text.Select formula for deeply flawed datasets, Power Query provides the right tool for the job. Mastering these text transformations is a fundamental skill that leads to more accurate, reliable, and professional-looking reports.

While perfecting these data cleaning steps is key for any Power BI project, it can be a time-consuming manual process. At Graphed you can automate this kind of data prep. When we connect to your marketing and sales data sources, we interpret and structure the data behind the scenes, so you don't have to spend your day in an editor removing special characters. You can simply ask questions in plain English, like "Show me revenue by campaign for the last 30 days," and instantly get dashboards and reports without worrying that a hidden symbol will throw off your numbers.