Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataEasy
A data analyst has a table in Power Query with a 'ProductCategory' column containing values like 'Electronics', 'Clothing ', ' Home Goods', 'Books'. The column has inconsistent leading and trailing spaces. Before loading the data into the model, the analyst needs to remove these extra spaces to ensure consistent categorization. Which Power Query transformation should be applied?
- ASplit Column
- BReplace Values
- CFormat > Trim
- DFormat > Clean
Show answer & explanationAnswer & explanation
Correct answer: C. Format > Trim
The 'Trim' transformation in Power Query specifically removes leading and trailing whitespace characters from text strings, which is exactly what is needed to standardize the 'ProductCategory' column.
Why the other options are wrong
- A. Split Column divides text into multiple columns based on a delimiter, which is not the goal here.
- B. Replace Values would require identifying and replacing each specific instance of spaces, which is inefficient and error-prone for varying leading/trailing spaces.
- D. Clean removes non-printable characters, not leading/trailing spaces.
Trim Transformation
A Power Query text transformation that removes all leading and trailing whitespace characters from text values in a column.
- Standardizes text by removing outer spaces.
- Does not affect spaces within the text string.
- Crucial for accurate grouping and filtering.
Memory trick: To clean up the ends, 'Trim' is your friend.