Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataHard
A financial analyst is preparing a dataset in Power Query where a column named 'Transaction_Value' is currently formatted as text, with some values containing currency symbols (e.g., '$1,234.56', '€500.00', '1.000,00'). Before performing any calculations, the analyst needs to convert this column to a numeric data type, ensuring that all currency symbols and thousand separators are handled correctly, regardless of their specific type or locale. Which sequence of Power Query transformations is most effective?
- A1. Replace Values (remove '$', '€'). 2. Replace Values (remove ','). 3. Change Type to Decimal Number.
- B1. Use Locale (set to 'English (United States)'). 2. Change Type to Decimal Number.
- C1. Change Data Type to Text. 2. Replace Values (remove all non-numeric characters). 3. Change Type to Decimal Number.
- D1. Change Type to Decimal Number using Locale (e.g., 'English (United States)'). 2. Apply Custom Column with `Number.FromText`.
Show answer & explanationAnswer & explanation
Correct answer: A. 1. Replace Values (remove '$', '€'). 2. Replace Values (remove ','). 3. Change Type to Decimal Number.
To robustly handle varying currency symbols and thousand separators, individual replacement steps are often necessary to remove non-numeric characters that Power Query's default type conversion or locale settings might not universally interpret. After cleaning, converting to 'Decimal Number' is straightforward.
Why the other options are wrong
- B. Using locale for type conversion is effective for *consistent* locale formatting but might fail if the column contains a mix of currency symbols or thousand separators from different locales (e.g., '$1,234.56' and '1.000,00').
- C. Removing 'all non-numeric characters' would also remove decimal points ('.') in some cases, leading to incorrect values. Changing type to text first is also unnecessary if it's already text.
- D. Applying `Number.FromText` after setting a locale for type change is redundant and `Number.FromText` would also need a locale parameter to handle specific formats, not a mix.
Robust Numeric Type Conversion
The process of converting a text column to a numeric type in Power Query, specifically addressing inconsistent non-numeric characters (like currency symbols, varying thousand separators) that hinder direct conversion.
- Direct type conversion often fails with mixed non-numeric characters.
- Explicit 'Replace Values' steps can pre-clean the data.
- Locale settings are useful but may not cover all mixed formats.
- Order of operations (clean then convert) is crucial.
Memory trick: Clean the text first, then convert the numeric thirst.