2.3 Data Types and Type Detection

Quick Answer: Power BI auto-detects types from the first 200 rows, which can be wrong. Always verify and manually set types. Use Fixed Decimal for currency (exact, 4 decimal places), Decimal for general numbers (floating point), and Text for identifiers like zip codes and product codes that should never be aggregated.

Power BI Data Types

Automatic Type Detection

When you first connect to a data source, Power Query automatically detects column types based on the first 200 rows:

How Auto-Detection Works

1. Power Query scans the first 200 values in each column
2. It attempts to identify the most appropriate type
3. A "Changed Type" step is automatically added to the query
4. If a column has mixed types in the first 200 rows, Text is assigned

Problems with Auto-Detection

Decimal Number vs. Fixed Decimal Number

This distinction is frequently tested:

Decimal Number (Floating Point)

• Uses IEEE 754 double-precision (64-bit floating point)
• Can represent very large and very small numbers
• Subject to floating-point rounding errors

Example of rounding issue:

$$0.1 + 0.2 = 0.30000000000000004 \text{ (not exactly 0.3)}$$

Fixed Decimal Number (Currency)

• Uses exact decimal arithmetic with up to 4 decimal places
• No floating-point rounding errors
• Range: -922,337,203,685,477.5808 to 922,337,203,685,477.5807

Best practice: Always use Fixed Decimal Number for monetary values (prices, salaries, revenue) to avoid rounding errors in financial calculations.

Setting Types Manually

In Power Query Editor

Select column → Transform tab → Data Type → Choose correct type

Or click the type icon in the column header (ABC/123/calendar icon).

Using Locale for Regional Formats

When dates or numbers use non-US formatting:

Right-click column → Change Type → Using Locale
→ Select data type and locale (e.g., Date with English (United Kingdom) for DD/MM/YYYY)

This is critical for:

• Date formats: US (MM/DD/YYYY) vs. European (DD/MM/YYYY)
• Decimal separators: US uses period (1,234.56) vs. European comma (1.234,56)

Type Impact on Model Behavior

Best Practices for Type Management

1. Set types in the first step after connecting to data (before other transforms)
2. Delete the auto-generated "Changed Type" step and create your own explicit one
3. Use Fixed Decimal for all currency/monetary fields
4. Keep identifier columns as Text (ZIP codes, product codes, employee IDs)
5. Use Date (not Date/Time) when time component is not needed — reduces storage
6. Profile the entire dataset before setting types to catch edge cases

Type Detection and the "Changed Type" Step

A subtle but heavily tested behavior: when you connect to a source, Power Query silently appends a "Changed Type" step inferred from the first 200 rows. If you later add or rename columns, this auto step can throw "the column X of the table wasn't found" errors or quietly re-coerce a column you meant to keep as Text. Best practice is to delete the auto-generated Changed Type step and add your own explicit one as the first action, so type assignment is intentional and survives schema changes.

Exam Tip: Identifiers that look numeric but are never summed — ZIP codes, phone numbers, product SKUs, account numbers — should be Text. Keeping them numeric not only drops leading zeros but also gives them a misleading default Sum aggregation in report visuals.

On the Exam

The PL-300 frequently tests:

• Choosing between Decimal Number and Fixed Decimal Number for currency
• Identifying problems caused by automatic type detection
• Using the "Using Locale" option for international date and number formats
• Understanding how data types affect relationships and DAX calculations
• Knowing when to use Text type for identifiers that look like numbers