Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataEasy
A data analyst is preparing a dataset in Power Query where a column named `ProductCategory` contains values like 'Electronics ', ' Clothing', 'Books'. Due to inconsistent data entry, some values have leading or trailing whitespace. The analyst needs to clean this column to ensure all values are standardized (e.g., 'Electronics', 'Clothing', 'Books') before loading into the data model. Which Power Query transformation should be applied?
- AFormat
- BReplace Values
- CTrim
- DClean
Show answer & explanationAnswer & explanation
Correct answer: C. Trim
The 'Trim' transformation in Power Query is specifically designed to remove leading and trailing whitespace from text values, directly addressing the problem of inconsistent spacing. 'Clean' removes non-printable characters, 'Format' changes casing, and 'Replace Values' targets specific strings.
Why the other options are wrong
- A. Format includes options like Uppercase, Lowercase, Capitalize Each Word, which are for casing, not whitespace.
- B. Replace Values would require knowing every possible whitespace variation, which is inefficient and error-prone for this scenario.
- D. Clean removes non-printable characters, not leading/trailing spaces.
Trim Transformation (Power Query)
A Power Query text transformation that removes all leading and trailing whitespace characters from text values in a column, standardizing string data.
- Removes spaces from start and end of string
- Essential for data cleaning and consistency
- Prevents matching issues and improves data quality
Memory trick: Trim the Text, Clean the Data.