Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data analyst is working with a large sales dataset in Power Query. The dataset includes a 'TransactionID' column which is currently stored as a Text data type. This column consists of unique identifiers that are always positive whole numbers. To improve performance and reduce memory usage in the Power BI model, the analyst wants to convert this column to the most efficient numeric data type. Which data type should the analyst choose?
- AWhole Number
- BText
- CFixed Decimal Number
- DDecimal Number
Show answer & explanationAnswer & explanation
Correct answer: A. Whole Number
Since 'TransactionID' consists of unique identifiers that are always positive whole numbers and do not require decimals, 'Whole Number' is the most efficient and appropriate numeric data type. It uses less memory than 'Decimal Number' or 'Fixed Decimal Number'.
Why the other options are wrong
- B. Text is the current inefficient data type and needs to be converted for performance and proper numerical operations.
- C. Fixed Decimal Number is specifically for financial calculations with fixed precision, not general whole number IDs.
- D. Decimal Number allows for decimal places, which is unnecessary for whole number IDs and uses more memory.
Optimized Integer Data Types (Power Query)
Selecting the smallest appropriate integer data type (e.g., Whole Number) for columns containing only whole numbers in Power Query to improve performance, reduce memory footprint, and ensure correct numerical operations in Power BI.
- Whole Number is efficient for integer values.
- Avoid Decimal or Fixed Decimal for pure integers.
- Choosing the right type reduces model size and query time.
- Important for columns like IDs, counts, or ranks.
Memory trick: Types define data's fate, choose wisely, don't hesitate.