Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataEasy
A data analyst is working with a table in Power Query where a 'ProductCategory' column contains values like ' Electronics ', 'Clothing ', and ' Food '. Due to inconsistent data entry, some values have leading or trailing spaces. These spaces are causing issues with grouping and filtering. Which Power Query transformation should the analyst use to remove these unwanted spaces?
- AReplace Values
- BClean
- CTrim
- DSplit Column
Show answer & explanationAnswer & explanation
Correct answer: C. Trim
The 'Trim' transformation is specifically designed to remove leading and trailing whitespace characters from text values, directly addressing the problem of inconsistent spacing for grouping and filtering.
Why the other options are wrong
- A. 'Replace Values' could be used, but it would require specifying each type of space (e.g., ' ', ' ') and is less efficient and robust than 'Trim' for this specific task.
- B. 'Clean' removes non-printable characters, not spaces.
- D. 'Split Column' divides a column into multiple columns based on a delimiter, which is not the goal here.
Trim Transformation (Power Query)
A Power Query text transformation that removes all leading and trailing whitespace characters from a text string. It is essential for standardizing text data and ensuring accurate comparisons, grouping, and filtering.
- Removes spaces from start and end of text.
- Does not remove spaces within the text string.
- Crucial for data standardization and consistency.
Memory trick: Trim the fat, not the meat.