Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataHard
A data modeler is preparing a dataset in Power Query where a column named 'ProductCost' contains numeric values. However, upon profiling, it's discovered that some cells contain text values like 'N/A' or 'Unknown' instead of numbers, which causes errors during type conversion to a decimal number. The modeler needs to convert this column to a numeric type, treating 'N/A' and 'Unknown' as nulls, without losing the rest of the valid numeric data. Which is the most robust Power Query approach to achieve this?
- ASet the data type to Decimal Number and then use 'Replace Errors' to replace errors with null.
- BUse 'Transform > Any Column > Parse' and specify a numeric format.
- CUse 'Add Conditional Column' to check if values are numeric, then convert, otherwise set to null.
- DUse 'Replace Values' to replace 'N/A' and 'Unknown' with null, then set the data type to Decimal Number.
Show answer & explanationAnswer & explanation
Correct answer: D. Use 'Replace Values' to replace 'N/A' and 'Unknown' with null, then set the data type to Decimal Number.
Replacing specific non-numeric text values ('N/A', 'Unknown') with nulls *before* attempting the type conversion is the most robust approach. This pre-cleans the column, allowing the subsequent type conversion to Decimal Number to succeed for all valid numeric entries without errors.
Why the other options are wrong
- A. Setting the type first would cause errors for 'N/A' and 'Unknown', which then need to be handled. While 'Replace Errors' works, pre-replacing known text values is often cleaner and more explicit.
- B. 'Parse' attempts to interpret text as a specific type but might still fail or produce errors for 'N/A' or 'Unknown' without explicit handling.
- C. Using a conditional column to check for numeric values is overly complex for this task and less efficient than direct replacement and type conversion.
Robust Numeric Type Conversion (Power Query)
A systematic approach in Power Query to convert a column to a numeric data type, ensuring that non-numeric values are handled gracefully (e.g., converted to nulls) to prevent errors and data loss.
- Identify all non-numeric text values first.
- Replace non-numeric text with nulls BEFORE type conversion.
- Use 'Replace Values' for known text, or 'Replace Errors' for unexpected conversion failures.
- Ensures data integrity and prevents downstream calculation issues.
Memory trick: Mixed types are tough, clean the text, then convert, that's enough.