How to Trim Text in Power BI

Nothing brings an analysis to a screeching halt faster than messy data. If your filters aren't working or your charts look strange, the culprit is often invisible - unwanted spaces lurking at the beginning or end of your text. This article will show you exactly how to trim text in Power BI using both Power Query and DAX, so you can clean your data and get back to finding meaningful insights.

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 Is Trimming Text So Important?

In a perfect world, all data would arrive perfectly formatted. In reality, data imported from different systems, web forms, or copied-and-pasted spreadsheets is often inconsistent. It’s common to find entries with extra blank spaces you can’t even see.

For example, to a database, "California", " California", and "California " are three distinct values. These hidden spaces can cause a whole host of problems in your reports:

• Inaccurate Counts: A count of unique states might show "California" multiple times, throwing off your numbers.
• Failed Relationships: If you try to join two tables on a state column, the relationship will fail for rows where one has "CA" and the other has "CA ".
• Broken Filters: A slicer for "USA" won't filter data labeled " USA", leading users to believe data is missing.
• Messy Visuals: Chart labels and table entries will look misaligned and unprofessional due to inconsistent padding.

Cleaning up these hidden characters is an essential part of the data preparation process. Fortunately, Power BI gives you powerful and easy-to-use tools to handle it.

Method 1: Using Power Query to Trim Text

The best place to clean and transform your data is in the Power Query Editor. This method cleans the data before it even loads into your data model. It's considered the best practice because it addresses the problem at the source, leading to better performance and a cleaner model. This modifies the actual data in the column for all future calculations.

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

The "Trim" Transformation (The Easy Way)

Power Query has a built-in feature to remove leading and trailing whitespace with just a couple of clicks. This is the simplest and most common method.

Here’s how to do it:

1. Click on "Transform data" from the Home ribbon in the main Power BI window. This will open the Power Query Editor.
2. From the query list on the left, select the table that contains the text column you want to clean.
3. Select the column by clicking on its header.
4. Navigate to the "Transform" tab in the ribbon.
5. In the "Text Column" section, click the "Format" dropdown menu.
6. Select "Trim" from the list.

Instantly, Power BI will remove any spaces from the beginning and end of every cell in that column. You'll see a new step called "Trimmed text" added to the "Applied Steps" pane on the right. This means your transformation is recorded and will be automatically applied every time the data refreshes.

The "Clean" Transformation

Sometimes, the issue isn't just spaces - it's non-printable characters like line breaks or tabs that get copied into your data. These can also cause formatting problems. Power Query’s "Clean" function is designed to remove them.

It's often a good idea to perform a "Clean" operation right after a "Trim" to ensure the text is as tidy as possible. The steps are nearly identical:

1. Select the same column you just trimmed.
2. Go back to the "Transform" tab.
3. Click the "Format" dropdown.
4. This time, select "Clean".

A new "Cleaned text" step will be added, scrubbing those pesky invisible characters from your data.

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

Using M Language Functions for More Control

For those who want more precise control, you can use Power Query’s underlying M language to create a custom column with the trimming logic.

The primary function is Text.Trim(text as nullable text), which does the same thing as the UI button. But there are also functions to trim only one side of the text:

• Text.TrimStart(): Removes whitespace only from the beginning of the text.
• Text.TrimEnd(): Removes whitespace only from the end of the text.

To use these, you would add a custom column:

1. While still in the Power Query Editor, go to the "Add Column" tab.
2. Click on "Custom Column".
3. In the dialog box, give your new column a name (e.g., "TrimmedProductName").
4. In the formula box, enter your M code. For example:

Text.Trim([YourColumnName])

This creates a new, cleaned column while leaving the original intact, which can be useful for auditing your data preparation steps.

Method 2: Using the TRIM Function in DAX

Another way to handle unwanted spaces is by using DAX (Data Analysis Expressions). This approach is generally used after your data is already loaded into Power BI. Instead of modifying the source data like Power Query, you create a new calculated column or measure that contains the cleaned-up version of the text.

The DAX function for this is simply TRIM().

Key Difference Between DAX TRIM() and Power Query Trim

It's important to understand a critical difference. The Power Query "Trim" transformation only removes spaces at the beginning and end of a string.

The DAX TRIM() function, however, does three things:

• Removes all spaces from the start of a string.
• Removes all spaces from the end of a string.
• Replaces any instance of multiple spaces between words with a single space.

For example, if your text is My Product Name, DAX TRIM() would convert it to My Product Name.

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

How to Use DAX TRIM() in a Calculated Column

Here is how to create a new column with the trimmed values:

1. Navigate to the "Data" view in Power BI (the grid icon on the left).
2. Select the table you want to work with from the "Fields" pane on the right.
3. In the "Table Tools" ribbon that appears at the top, click "New column".
4. The formula bar will appear. Enter your DAX formula, defining the name of your new column and the logic. For instance:

Cleaned Country = TRIM('Sales'[Country])

Press Enter, and a new "Cleaned Country" column will be added to your table with the extra spaces removed.

DAX vs. Power Query: Which Method to Choose?

So, you have two great options. Which one is right for you? Here’s a simple guide to help you decide.

Always Favor Power Query When...

• You are cleaning raw data. The fundamental principle of data modeling is to clean and shape your data as early as possible. Power Query is the tool designed for this.
• You want better performance. Cleaning data in Power Query means the Power BI engine has less work to do. The cleansed data is compressed more efficiently in the model, leading to faster reports.
• The change should be permanent. Transformations you make in Power Query become part of the data refresh process, ensuring your data is always pristine.

Use DAX When...

• You need a quick, temporary fix. If you can't go back and edit the query, using DAX can be a faster way to solve a problem in a specific visual or calculation.
• You need to handle extra spaces between words. If your data has issues like "Los Angeles" and you need "Los Angeles," the DAX TRIM() function is the only one that can handle that specific case.
• The trimming is part of a complex measure. Sometimes you need to trim text dynamically inside a measure. For example, trimming user input from a parameter before comparing it to a column in your data.

The general rule of thumb: Use Power Query for permanent data cleaning and preparation. Use DAX for dynamic calculations and special cases that Power Query can't handle.

Final Thoughts

Trimming extraneous spaces is a small but critical step in ensuring your Power BI reports are accurate, reliable, and professional. You can easily remove leading and trailing spaces with a few clicks in Power Query's "Format" menu, or you can leverage the DAX TRIM function to create cleaned calculated columns while also normalizing spaces between words.

We know that endless data cleaning and platform-hopping are some of the biggest time sinks in analytics. We built Graphed to eliminate that friction. Instead of manually trimming columns or running scripts, you simply connect your sources and then describe the dashboards and reports you need in plain English. Graphed automatically handles the nitty-gritty of data prep in the background, delivering clean, real-time insights in seconds, not hours.