Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataEasy
A Power BI developer is importing data from a customer relationship management (CRM) system. The data contains a column named 'CustomerStatus' with values like 'Active', 'Inactive', 'Pending', and 'ACTIVE'. The developer needs to ensure that 'Active' and 'ACTIVE' are treated as the same category for reporting purposes. Which Power Query transformation should be applied to standardize this column?
- AFormat > Uppercase to convert all values to 'ACTIVE'.
- BReplace Values to change 'ACTIVE' to 'Active'.
- CFormat > Capitalize Each Word to ensure consistent casing.
- DFormat > Lowercase to convert all values to 'active'.
Show answer & explanationAnswer & explanation
Correct answer: D. Format > Lowercase to convert all values to 'active'.
To ensure 'Active' and 'ACTIVE' are treated identically, converting all values to a consistent casing (either uppercase or lowercase) is the most effective approach. Lowercase is a common choice for standardization.
Why the other options are wrong
- A. While Uppercase would work, Lowercase is also a valid and common standardization choice, and equally effective in this scenario.
- B. Replacing values is a manual process and might miss other casing variations (e.g., 'AcTivE'). It's not scalable for many variations.
- C. Capitalize Each Word would result in 'Active', 'Inactive', 'Pending', and 'Active', which correctly unifies 'Active' and 'ACTIVE'. This is also a viable option but Lowercase is equally valid and directly addresses the problem.
Standardizing Text Casing (Power Query)
A set of Power Query transformations (Uppercase, Lowercase, Capitalize Each Word) used to ensure consistency in text data, which is crucial for accurate filtering, grouping, and merging.
- Prevents issues caused by case-sensitive comparisons.
- Common options: Uppercase, Lowercase, Capitalize Each Word.
- Applied via the 'Transform' tab in Power Query Editor.
Memory trick: Casing is like clothing; everyone needs to wear the same uniform for consistency.