A data analyst is working with a large sales dataset in Power Query. The dataset includes a 'TransactionID' column, which is a unique identifier. To improve query performance and reduce the data model size, the analyst wants to ensure that this column is stored as the smallest possible whole number type, given that `TransactionID` values range from 1 to 1,500,000. Which Power Query transformation should be used to achieve this and what is the most appropriate data type?
- AChange Type to Whole Number (Int64)
- BChange Type to Fixed Decimal Number
- CChange Type to Whole Number (Int32)
- DChange Type to Decimal Number
Show answer & explanationAnswer & explanation
Correct answer: A. Change Type to Whole Number (Int64)
A 'Whole Number' type (Int64) is appropriate for integers. While Int32 has a maximum value of 2,147,483,647, it's safer to use Int64 for values up to 1,500,000 to avoid potential overflow issues if the number of transactions increases significantly in the future, and because Power Query's 'Whole Number' typically maps to Int64 in the M language for robustness.
Why the other options are wrong
- B. Fixed Decimal Number is specifically for financial calculations to avoid floating-point inaccuracies, not for general integer IDs.
- C. Int32 supports values up to 2,147,483,647. While 1,500,000 fits, Power Query's default 'Whole Number' is often Int64 for broader compatibility and future-proofing, and explicitly choosing Int32 is not a direct option in the UI without M code. If the range was strictly below 32-bit limits, it could be considered, but Int64 is the safer and often default 'Whole Number' behavior.
- D. Decimal Number is for values with decimal places and is less efficient for integers, increasing storage size unnecessarily.
Optimized Integer Data Types
Optimized integer data types in Power BI (e.g., Whole Number, which can map to Int64 or Int32) are chosen to store whole numbers efficiently. Selecting the smallest appropriate type reduces model size and improves performance, while ensuring the type can accommodate the full range of values.
- Reduces model size and improves performance.
- Int32: stores up to ~2 billion.
- Int64 (Whole Number): stores up to ~9 quintillion.
- Choose smallest type that safely accommodates max value.
Memory trick: Size matters for speed, but safety first.