How to Remove Spaces in Power BI

Extra spaces in your data can quietly sabotage your Power BI reports, leading to broken relationships between tables, incorrect counts, and filtering errors that are a nightmare to diagnose. This guide breaks down the simple, effective methods for removing unwanted spaces using both the Power Query Editor and DAX, ensuring your data is clean, accurate, and ready for analysis.

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 Removing Spaces in Power BI is So Important

You might be wondering if a few extra spaces are really a big deal. In data analysis, they absolutely are. Tidy data is the bedrock of reliable reporting, and stray spaces can cause significant issues that aren't always obvious at first glance.

Here's why you should make cleaning up spaces a regular part of your process:

• Impact on Relationships: Power BI creates relationships between tables based on matching values in key columns. If "Product123 " (with a trailing space) in your Sales table is trying to connect with "Product123" in your Products table, Power BI sees them as two completely different values. The relationship will fail, and your visuals will show incorrect or incomplete data.
• Inaccurate Aggregations: Functions like COUNT or DISTINCTCOUNT treat values with different spacing as unique. If you're counting unique customer names, "John Smith", " John Smith", and "John Smith" would be counted as three separate customers, throwing off your metrics.
• Filtering and Slicer Problems: When a user tries to filter for "New York" in a slicer, records with "New York " might be excluded, leading them to believe certain data is missing when it’s just poorly formatted.
• Poor Sorting: Alphabetical sorting can also be affected. Values starting with a space (leading spaces) will often appear at the top of a sorted list, separating them from their correctly formatted counterparts and making lists look messy and unprofessional.

Simply put, cleaning up spaces isn't just about appearances, it’s about ensuring the foundational integrity of your data model and the accuracy of the insights you draw from it.

Choose Your Tool: Power Query Editor vs. DAX

In Power BI, you have two primary environments for data manipulation, and you can remove spaces in either one. Understanding the difference is key to building an efficient and maintainable report.

1. The Power Query Editor: This is the data transformation engine (the "T" in ETL - Extract, Transform, Load) that prepares your data before it gets loaded into your Power BI data model. Cleaning spaces here is considered the best practice because you fix the problem at the source. The changes are applied once during data refresh, making your model cleaner and more performant.
2. DAX (Data Analysis Expressions): This is the formula language used within the Power BI data model to create calculated columns and measures. You can use DAX functions to remove spaces, but this happens after the data has been loaded. This approach is useful for specific scenarios but is generally less efficient than using Power Query.

For most data cleaning tasks, the Power Query Editor is the right choice. Let's start there.

Method 1: Removing Spaces with the Power Query Editor

Using Power Query is the most direct and robust way to handle unwanted spaces. You are cleaning the raw data itself, which prevents formatting issues from ever reaching your report canvas. Open the Power Query Editor by clicking "Transform data" on the Home ribbon in Power BI Desktop.

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

Remove Leading and Trailing Spaces with Trim

Leading (at the beginning) and trailing (at the end) spaces are the most common culprits. They are invisible in a table view but can wreak havoc on your data relationships. Power Query has a built-in function called "Trim" that handles this perfectly.

Scenario: Your "Product SKU" column has values like " SKU-A " instead of "SKU-A".

Step-by-Step Instructions:

1. In the Power Query Editor, find and select the column you want to clean. You can select multiple text columns at once by holding down the Ctrl key.
2. Make sure the "Transform" tab is selected in the ribbon at the top.
3. Click on the Format button in the "Text Column" section.
4. From the dropdown menu, select Trim.

That's it. Power Query instantly removes all spaces from the beginning and end of every cell in your selected column(s). A new "Trimmed Text" step will be added to your "Applied Steps" pane on the right, documenting your work.

Remove ALL Spaces with Replace Values

Sometimes you need to remove every space in a string, including those in the middle. This is common with product codes, phone numbers, or postal codes that should not contain spaces.

Scenario: You imported product codes that look like "PN 555 01", but they should be "PN55501" for matching.

Step-by-Step Instructions:

1. Select the column you need to modify.
2. Right-click on the column header and choose Replace Values. (Alternatively, you can find it under the "Transform" tab).
3. The "Replace Values" window will appear.
4. In the "Value To Find" box, press the spacebar once.
5. Leave the "Replace With" box completely empty. Do not type a space in it.
6. Click OK.

All spaces - leading, trailing, and internal - will be removed from the cells in that column.

Combine Trim and Replace for Excess Internal Spaces

What about messy data entry with multiple spaces between words? For instance, a customer name might be entered as "Jane Doe" instead of "Jane Doe". You want to keep the single space but get rid of the extra one.

Scenario: Your data contains entries like "New York" with inconsistent internal spacing.

Step-by-Step Instructions:

1. First, Trim the column. This is always a good first step to remove any leading or trailing spaces. Select the column, go to Transform > Format > Trim.
2. Now, replace the double spaces. With the same column selected, go to Transform > Replace Values.

This process will replace every instance of " " with " ". If you have instances with three or more spaces, you may need to repeat the "Replace Values" step until no double spaces are left. Power Query is usually smart enough to handle this iteratively in a single step, but if issues persist, repeating the step is a reliable fix.

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

Remove Non-Printing Characters with Clean

Sometimes, what looks like a space isn’t a space at all - it's a non-printing character like a tab, a line break, or other strange artifacts copied from a web page or another system. These can cause the same problems but won't be fixed by Trim or Replace Values. Power Query has another convenient fix for this.

Step-by-Step Instructions:

1. After selecting your column, navigate to the Transform tab.
2. Click on Format.
3. Select Clean.

The "Clean" function removes all non-printing characters from the text. It's often best to run Clean first and then Trim to ensure your text columns are a perfectly standardized format.

Method 2: Removing Spaces with DAX

While Power Query is the preferred method for data cleansing, there are times when using DAX makes sense. You might use DAX if:

• You don't have access to modify the data source via Power Query.
• You need to preserve the original, unaltered column for another purpose.
• You are creating a quick calculation for a single visual and a new column is easier.

DAX modifications create a Calculated Column, which means the cleaned data exists alongside the original data within your model. This consumes more memory, so use it judiciously.

Remove Leading and Trailing Spaces with TRIM()

The DAX TRIM() function works exactly like its Power Query counterpart, removing spaces from the beginning and end of a text string.

To create a new calculated column:

1. Go to the "Data" view in Power BI Desktop.
2. Select the table you want to add the column to.
3. In the "Table tools" ribbon, click on New column.
4. Enter the following formula in the formula bar, replacing the table and column names with your own:

StateClean_Trim = TRIM('Orders'[State])

This creates a new column called "StateClean_Trim" that contains the values from the 'Orders'[State] column, but without any leading or trailing spaces.

Remove All Spaces with SUBSTITUTE()

To remove all spaces, including internal ones, you'll use the DAX SUBSTITUTE() function. This function finds text within a string and replaces it with another.

To create a new calculated column to remove all spaces:

1. Follow the steps above to create a new column.
2. Use this formula format:

ProductCodeClean = SUBSTITUTE('Products'[Product Code], " ", "")

In this formula, DAX looks in the 'Products'[Product Code] column, finds every instance of a single space (" "), and replaces it with an empty string ("") — effectively deleting it.

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

Handling Excess Internal Spaces with Nested SUBSTITUTE()

To fix the "Jane Doe" problem in DAX, you can use SUBSTITUTE() as well. However, since SUBSTITUTE() replaces an exact string, you must also be exact. A common approach is to nest the functions.

First, replace double spaces with a single space. Then, run that logic again to catch any remaining multiples.

Formula to fix double (or more) spaces:

CustomerName_Clean = TRIM(SUBSTITUTE('Customers'[FullName], " ", " "))

For data that is extremely messy with triple or more spaces, nesting the SUBSTITUTE function is a powerful technique. Wrapping the final result in a TRIM ensures no sneaky leading/trailing spaces remain.

Power Query vs. DAX: Which Should You Choose?

To summarize, here's a quick guide on when to use each method:

Use Power Query When:

• You are doing foundational data cleaning. This is your default, go-to choice.
• You want to improve model performance. Cleaning data before it's loaded results in a more efficient data model.
• You don't need the original 'dirty' data for any reason.
• The change should apply to the entire report permanently.

Use DAX When:

• The original column needs to be kept intact for other calculations or for display.
• You are creating a dynamic measure or a calculation for a specific visual.
• You lack permission to edit the data source through Power Query.
• You're performing a quick, one-off analysis and a calculated column is faster than going through the data refresh process.

A good rule of thumb is: "Transform in Power Query, calculate in DAX." Since removing spaces is a transformation (a cleaning task), Power Query is almost always the better answer.

Final Thoughts

Cleaning up unwanted spaces in Power BI is a fundamental step toward building accurate, reliable, and professional-looking reports. By mastering the Trim, Clean, and Replace functions in Power Query, you can fix formatting issues at the source, which is the best practice. For situations that require more flexibility or preservation of original data, the TRIM and SUBSTITUTE functions in DAX provide a powerful alternative.

Streamlining tasks like these is a huge part of effective data analysis, but it's just one piece of the puzzle. At Graphed, we created a tool that automates the whole reporting lifecycle. We make it easy to connect all your marketing and sales data sources in one place, and instead of clicking through menus to clean data and build charts, you just describe what you need in plain English. This lets you move from raw data to actionable insights in seconds, giving you back time to focus on strategy instead of struggling with report-building busywork.