Why Power Query transforms messy data in Excel - Formula Trace Breakdown

Sample Data

This data shows a list of people with their names, ages, and cities. The data is messy: names have extra spaces, ages have text instead of numbers, and cities have extra spaces.

Cell	Value
A1	Name
B1	Age
C1	City
A2	Alice
B2	25
C2	New York
A3	Bob
B3	Thirty
C3	Los Angeles
A4	Charlie
B4	40
C4	Chicago

Formula Trace

=TRIM(A2)

Step 1: A2

Step 2: TRIM(" Alice ")

Cell Reference Map

   A       B       C  
1 Name    Age    City 
2 Alice   25     New York
3 Bob     Thirty Los Angeles
4 Charlie 40     Chicago 

The formula references cell A2 which contains the name with extra spaces.

Result

   A       B       C  
1 Name    Age    City 
2 Alice   25     New York
3 Bob     Thirty Los Angeles
4 Charlie 40     Chicago 

After applying TRIM to A2, the extra spaces around 'Alice' are removed, cleaning the data for better use.

Sheet Trace Quiz - 3 Questions

What does the TRIM function do in the formula =TRIM(A2)?

A Changes text to uppercase

B Removes extra spaces from the text

C Converts text to a number

D Adds spaces around the text

Key Result

TRIM removes extra spaces from text values to clean data.