Trim and clean text in Power BI - Cell-by-Cell Formula Trace

Sample Data

This column contains text values with extra spaces, tabs, and line breaks that need cleaning.

Cell	Value
A1	Hello
A2	World
A3	Power BI
A4	Data
A5	Clean Text

Formula Trace

CLEAN(TRIM(A2))

Step 1: TRIM(A2)

Step 2: CLEAN("World\n")

Cell Reference Map

     A     
1 |  '  Hello  '  
2 |  '  World\n' 
3 |  '\tPower BI\t' 
4 |  '  Data  \n' 
5 |  ' Clean\tText ' 

Formula references cell A2 which contains '  World\n'

The formula uses the text in cell A2 which has spaces and a line break.

Result

     A       |    B    
1 |  '  Hello  ' |        
2 |  '  World\n' | 'World' 
3 |  '\tPower BI\t' |      
4 |  '  Data  \n' |       
5 |  ' Clean\tText ' |      

The result of CLEAN(TRIM(A2)) is 'World' shown in column B next to the original text.

Sheet Trace Quiz - 3 Questions

What does the TRIM function do to the text in A2?

A Removes all letters except first

B Removes extra spaces from start and end

C Converts text to uppercase

D Adds spaces between words

Key Result

CLEAN(TRIM(text)) removes extra spaces and non-printable characters from text