Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataEasy
A data analyst is importing customer data from a legacy system into Power BI. The data includes a 'CustomerID' column where some entries contain leading or trailing whitespace characters due to inconsistent data entry. These extra spaces prevent accurate matching and filtering. Which Power Query transformation should the analyst use to remove these unnecessary characters?
- AClean
- BExtract
- CReplace Values
- DTrim
Show answer & explanationAnswer & explanation
Correct answer: D. Trim
The 'Trim' transformation in Power Query specifically removes leading and trailing whitespace characters from text values in a column, which is exactly what is needed to standardize the 'CustomerID' column.
Why the other options are wrong
- A. Clean removes non-printable characters, not leading/trailing whitespace.
- B. Extract is used to pull specific parts of a text string, not to remove whitespace from the ends.
- C. Replace Values would require knowing all possible variations of whitespace to replace, which is inefficient and error-prone compared to Trim.
Trim Transformation (Power Query)
A Power Query transformation that removes all leading and trailing whitespace characters from text values in a selected column.
- Essential for data cleaning and standardization.
- Helps ensure accurate matching, filtering, and grouping.
- Does not remove spaces within the text string.
Memory trick: Text clean-up, trim the ends, format the case, make it friends.