Trim and clean text in Power BI - Step-by-Step Guide

Introduction

This feature helps you remove extra spaces and unwanted characters from text data. It makes your text clean and neat for better analysis and display in reports.

When your sales data has extra spaces before or after product names that cause mismatches.

When customer feedback contains hidden line breaks or tabs that affect text filters.

When you want to standardize addresses by removing extra spaces and non-printable characters.

When importing data from Excel or CSV files that include irregular spacing.

When preparing text fields for joining tables or creating slicers.

Steps

Step 1: Click

- Modeling tab

The ribbon shows options to create new columns or measures

Step 2: Click

- New column

A formula bar appears to write a DAX expression for the new column

Step 3: Type

- Formula bar

You enter a formula using TRIM and CLEAN functions to clean text

💡 Use syntax: CleanedText = TRIM(CLEAN(TableName[ColumnName]))

Step 4: Press Enter

- Formula bar

A new column appears in the data view with cleaned and trimmed text

Step 5: Use

- Report view

The cleaned text column can be used in visuals and slicers without extra spaces or hidden characters

Before vs After

Before

Column 'Product' has values like ' Apple ', 'Banana\n', and ' Orange\t'

After

New column 'CleanedText' shows 'Apple', 'Banana', and 'Orange' without extra spaces or hidden characters

Settings Reference

TRIM function

📍 DAX formula bar

To remove extra spaces before, after, and between words

Default: N/A

CLEAN function

📍 DAX formula bar

To remove hidden characters that can cause display or filtering issues

Default: N/A

Common Mistakes

Using only TRIM without CLEAN

TRIM removes extra spaces but does not remove hidden characters like line breaks

Use CLEAN inside TRIM like TRIM(CLEAN(Column)) to remove both spaces and hidden characters

Applying TRIM and CLEAN on numeric columns

These functions work on text data and can cause errors or unexpected results on numbers

Convert numbers to text first if cleaning is needed, or apply only on text columns

Summary

Trim and clean text removes extra spaces and hidden characters from text data.

Use TRIM to remove extra spaces and CLEAN to remove non-printable characters.

Apply these functions in a new column using DAX for clean, consistent text in reports.