Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataEasy
A data analyst is preparing a dataset in Power Query for a sales report. The dataset includes a 'ProductCode' column that contains unique identifiers for products. The analyst notices that some product codes are entered with inconsistent casing (e.g., 'ABC123', 'abc123', 'Abc123'). To ensure that these are treated as the same product for analysis, the analyst needs to standardize the casing of this column. Which Power Query transformation should be applied?
- AFormat > Uppercase
- BFormat > Capitalize Each Word
- CFormat > Trim
- DReplace Values
Show answer & explanationAnswer & explanation
Correct answer: A. Format > Uppercase
To standardize casing for unique identifiers like product codes, converting all values to either 'Uppercase' or 'Lowercase' is the most effective method, as it ensures all variations of a code are treated identically.
Why the other options are wrong
- B. Capitalize Each Word would make 'abc123' into 'Abc123', but 'ABC123' would remain 'ABC123', still leaving inconsistencies.
- C. Trim removes leading/trailing spaces, which is not the issue here.
- D. Replace Values would require identifying every possible casing variation and replacing it, which is impractical for standardization.
Standardizing Text Casing
The process of converting all text values in a column to a consistent letter case (e.g., all uppercase or all lowercase) to ensure proper grouping, filtering, and matching.
- Crucial for consistent unique identifiers and categorical data.
- Uppercase or Lowercase transformations are commonly used.
- Prevents different casings from being treated as distinct values.
Memory trick: For consistent IDs, choose one case and abide.