Power BI - Data Cleaning with Power Query
You have a text column with irregular spaces and hidden control characters. Which DAX formula best standardizes the text by removing these issues?
You have a text column with irregular spaces and hidden control characters. Which DAX formula best standardizes the text by removing these issues?
hard📝 Scenario Q8 of 15
Step-by-Step Solution
Solution:
Step 1: Understand function order
CLEAN removes non-printable characters; TRIM removes extra spaces at edges.Step 2: Analyze options
Applying CLEAN first removes hidden chars, then TRIM removes spaces. TRIM(CLEAN([TextColumn])) applies TRIM after CLEAN, which is best.Step 3: Evaluate other options
CLEAN(TRIM([TextColumn])) reverses order, which may leave spaces caused by hidden chars. SUBSTITUTE([TextColumn], " ", "") removes all spaces, not just extra. UPPER(TRIM([TextColumn])) changes case unnecessarily.Final Answer:
TRIM(CLEAN([TextColumn])) -> Option CQuick Check:
Clean first, then trim spaces for best results. [OK]
Quick Trick: Apply CLEAN before TRIM to fix text issues [OK]
Common Mistakes:
- Reversing CLEAN and TRIM order
- Removing all spaces instead of trimming
- Changing text case unnecessarily
Master "Data Cleaning with Power Query" in Power BI
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore Power BI Quizzes