Trim and clean text in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Removing Extra Spaces with TRIM

Given a column 'CustomerName' with values like ' John Doe ', which DAX expression correctly removes all leading and trailing spaces but keeps single spaces between words?

A TRIM(CLEAN('Table'[CustomerName]))

B CLEAN('Table'[CustomerName])

C SUBSTITUTE('Table'[CustomerName], " ", "")

D TRIM('Table'[CustomerName])

💡 Hint

Think about which function removes spaces only at the start and end, not inside the text.

TRIM removes extra spaces at the start and end but keeps single spaces between words. CLEAN removes non-printable characters but does not trim spaces. SUBSTITUTE removes all spaces, which is not desired here.

❓ visualization

intermediate

Visualizing Cleaned Text Length

You want to create a Power BI card visual that shows the average length of cleaned product descriptions. Which measure correctly calculates the average length after trimming and cleaning the 'ProductDescription' column?

A AverageLength = AVERAGEX('Products', LEN('Products'[ProductDescription]))

B AverageLength = AVERAGEX('Products', LEN(TRIM(CLEAN('Products'[ProductDescription]))))

C AverageLength = AVERAGE(LEN(TRIM('Products'[ProductDescription])))

D AverageLength = AVERAGEX('Products', LEN(CLEAN('Products'[ProductDescription])))

💡 Hint

Remember to apply both TRIM and CLEAN before measuring length, and use AVERAGEX to iterate rows.

Option B correctly applies CLEAN and TRIM to each row's text, then measures length, and averages over all rows. Option B misses cleaning and trimming. Option B uses AVERAGE on a column of lengths which is invalid. Option B misses trimming.

❓ data_modeling

advanced

Handling Non-Printable Characters in Data Model

You notice your customer feedback text contains hidden non-printable characters causing filtering issues. What is the best approach to clean this text in your Power BI data model?

A Create a calculated column using CLEAN and TRIM functions on the feedback text.

B Apply only TRIM function to remove spaces from the feedback text.

C Use SUBSTITUTE to replace spaces with empty strings in the feedback text.

D Ignore cleaning and handle issues in report filters.

💡 Hint

Think about removing both non-printable characters and extra spaces.

CLEAN removes non-printable characters, and TRIM removes extra spaces. Using both ensures clean text for filtering. SUBSTITUTE removing spaces breaks words. Ignoring cleaning causes persistent issues.

🔧 Formula Fix

advanced

Debugging Unexpected Spaces in Text Output

A measure uses this DAX: CleanName = TRIM(CLEAN('Customers'[Name])) but the report still shows extra spaces inside names like 'John Doe'. What is the cause?

Power BI

CleanName = TRIM(CLEAN('Customers'[Name]))

A The column 'Name' contains tabs that CLEAN cannot remove.

B CLEAN does not remove spaces, so extra spaces remain inside the text.

C TRIM only removes leading and trailing spaces, not multiple spaces inside the text.

D The measure syntax is incorrect and causes errors.

💡 Hint

Consider what TRIM does and does not do.

TRIM removes spaces only at the start and end of text, not multiple spaces between words. CLEAN removes non-printable characters but not extra spaces. The syntax is correct. Tabs are removed by CLEAN.

🧠 Conceptual

expert

Best Practice for Cleaning Text in Large Datasets

In a large Power BI dataset, which approach is best to efficiently clean text columns by removing non-printable characters and trimming spaces without slowing report performance?

A Perform text cleaning in the data source or Power Query before loading data into the model.

B Use calculated columns with CLEAN and TRIM in the data model for all text columns.

C Apply CLEAN and TRIM in every measure that uses text columns dynamically.

D Ignore cleaning and rely on report users to handle text inconsistencies.

💡 Hint

Think about where cleaning is most efficient: before or after loading data.

Cleaning text before loading reduces model size and improves performance. Calculated columns and dynamic cleaning in measures increase model complexity and slow reports. Ignoring cleaning causes user confusion.

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