Trim and clean text in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to remove extra spaces from the text column.

Power BI

CleanText = TRIM([1])

Drag options to blanks, or click blank then click option'

ATable[BooleanColumn]

BTable[TextColumn]

CTable[DateColumn]

DTable[NumberColumn]

Attempts:

3 left

💡 Hint

Common Mistakes

Using TRIM on a number or date column causes errors.

Forgetting to specify the column name.

The TRIM function removes extra spaces from text. You must apply it to a text column.

2fill in blank

medium

Complete the code to remove non-printable characters from the text.

Power BI

CleanText = CLEAN([1])

Drag options to blanks, or click blank then click option'

ATable[TextColumn]

BTable[NumberColumn]

CTable[DateColumn]

DTable[BooleanColumn]

Attempts:

3 left

💡 Hint

Common Mistakes

Applying CLEAN to non-text columns.

Confusing CLEAN with TRIM.

The CLEAN function removes non-printable characters from text columns.

3fill in blank

hard

Fix the error in the code to trim and clean the text column.

Power BI

CleanText = TRIM(CLEAN([1]))

Drag options to blanks, or click blank then click option'

ATable[NumberColumn]

BTable[DateColumn]

CTable[TextColumn]

DTable[BooleanColumn]

Attempts:

3 left

💡 Hint

Common Mistakes

Using number or date columns inside TRIM or CLEAN.

Not nesting CLEAN inside TRIM correctly.

Both TRIM and CLEAN require a text column as input. Using a non-text column causes errors.

4fill in blank

hard

Fill both blanks to create a new column that trims and cleans the 'CustomerName' column.

Power BI

CleanName = [1]([2](Table[CustomerName]))

Drag options to blanks, or click blank then click option'

ATRIM

BCLEAN

CREMOVE

DSTRIP

Attempts:

3 left

💡 Hint

Common Mistakes

Reversing the order of TRIM and CLEAN.

Using functions that do not exist in Power BI.

The correct order is TRIM outside CLEAN to first remove non-printable characters, then trim spaces.

5fill in blank

hard

Fill all three blanks to create a measure that trims, cleans, and converts the 'ProductCode' column to uppercase.

Power BI

CleanCode = [1]([2]([3](Table[ProductCode])))

Drag options to blanks, or click blank then click option'

AUPPER

BTRIM

CCLEAN

DLOWER

Attempts:

3 left

💡 Hint

Common Mistakes

Incorrect function order causing wrong results.

Using LOWER instead of UPPER.

First CLEAN removes non-printable characters, then TRIM removes spaces, finally UPPER converts text to uppercase.

Practice

1. What does the TRIM function do in Power BI?

easy

A. Removes all numbers from text

B. Removes extra spaces from the start and end of text

C. Converts text to uppercase

D. Removes punctuation marks from text

Solution

1. Step 1: Understand the purpose of TRIM

   The TRIM function removes extra spaces from the start and end of a text string, making it neat.

2. Step 2: Compare with other options

   Options A, C, and D describe different text operations not related to trimming spaces.

3. Final Answer:

   Removes extra spaces from the start and end of text -> Option B

4. Quick Check:

   TRIM removes spaces = B [OK]

Hint: TRIM cleans spaces around text, not inside [OK]

Common Mistakes:

• Thinking TRIM removes spaces inside text
• Confusing TRIM with CLEAN
• Assuming TRIM changes text case

2. Which of the following is the correct syntax to remove non-printable characters from a column named Comments in Power BI?

easy

A. REMOVE(Comments)

B. TRIM(Comments)

C. STRIP(Comments)

D. CLEAN(Comments)

Solution

1. Step 1: Identify the function for non-printable characters

   The CLEAN function removes hidden non-printable characters from text.

2. Step 2: Verify syntax correctness

   CLEAN(Comments) is the correct syntax; TRIM removes spaces, REMOVE and STRIP are invalid in Power BI.

3. Final Answer:

   CLEAN(Comments) -> Option D

4. Quick Check:

   CLEAN removes non-printable chars = C [OK]

Hint: Use CLEAN to remove hidden characters, TRIM for spaces [OK]

Common Mistakes:

• Using TRIM instead of CLEAN for hidden characters
• Trying non-existent functions like REMOVE or STRIP
• Confusing syntax with other languages

3. Given the text value " Hello World " in a column, what will be the result of TRIM(CLEAN([Column]))?

medium

A. "Hello World"

B. " Hello World "

C. "Hello\u0007 World"

D. "HelloWorld"

Solution

1. Step 1: Apply CLEAN to remove non-printable characters

   CLEAN removes the hidden character \u0007, so text becomes " Hello World ".

2. Step 2: Apply TRIM to remove extra spaces

   TRIM removes spaces at start and end, resulting in "Hello World" with a single space between words.

3. Final Answer:

   "Hello World" -> Option A

4. Quick Check:

   TRIM + CLEAN cleans spaces and hidden chars = A [OK]

Hint: CLEAN first removes hidden chars, then TRIM removes spaces [OK]

Common Mistakes:

• Ignoring hidden characters and only trimming spaces
• Removing spaces inside words incorrectly
• Confusing escape characters with visible text

4. You wrote the formula TRIM(CLEAN([CustomerName])) but it returns an error. What is the most likely cause?

medium

A. The column name is misspelled or does not exist

B. TRIM cannot be nested inside CLEAN

C. CLEAN only works on numbers, not text

D. TRIM requires a numeric input

Solution

1. Step 1: Check function nesting rules

   TRIM and CLEAN can be nested; syntax is valid.

2. Step 2: Verify column name correctness

   If the column name is misspelled or missing, Power BI throws an error.

3. Final Answer:

   The column name is misspelled or does not exist -> Option A

4. Quick Check:

   Invalid column name causes error = D [OK]

Hint: Check column names carefully if formula errors occur [OK]

Common Mistakes:

• Assuming CLEAN or TRIM only work on numbers
• Thinking nesting functions causes errors
• Ignoring case sensitivity in column names

5. You have a column Feedback with messy text containing extra spaces and hidden characters. Which DAX formula correctly cleans and trims the text for a new calculated column?

hard

A. CleanTrim = TRIM([Feedback]) + CLEAN([Feedback])

B. CleanTrim = CLEAN(TRIM([Feedback]))

C. CleanTrim = TRIM(CLEAN([Feedback]))

D. CleanTrim = SUBSTITUTE(TRIM([Feedback]), CLEAN([Feedback]), "")

Solution

1. Step 1: Understand order of operations

   CLEAN should be applied first to remove hidden characters, then TRIM removes extra spaces.

2. Step 2: Evaluate each option

   CleanTrim = TRIM(CLEAN([Feedback])) applies CLEAN then TRIM correctly. CleanTrim = CLEAN(TRIM([Feedback])) reverses order, which may leave hidden chars. Options C and D misuse functions and produce incorrect results.

3. Final Answer:

   CleanTrim = TRIM(CLEAN([Feedback])) -> Option C

4. Quick Check:

   CLEAN then TRIM cleans text best = A [OK]

Hint: Always CLEAN first, then TRIM for best text cleaning [OK]

Common Mistakes:

• Applying TRIM before CLEAN
• Adding results instead of nesting functions
• Using SUBSTITUTE incorrectly to clean text