ExamFinch.com My Study

Study guide

2.3 Data Types and Type Detection

Practice this exam

Microsoft Power BI Data Analyst PL-300

Track your progress as you read

Finished sections get a check mark.

1 Introduction

About the PL-300 Exam The Power BI Ecosystem

2 Chapter 1: Get or Connect to Data

1.1 Data Source Types and Connections 1.2 Storage Modes: Import, DirectQuery, and Direct Lake 1.3 Data Source Settings and Parameters

3 Chapter 2: Profile and Clean the Data

2.1 Data Profiling in Power Query 2.2 Resolving Errors, Nulls, and Data Quality Issues 2.3 Data Types and Type Detection

4 Chapter 3: Transform and Load the Data

3.1 Column Transformations and Data Types 3.2 Grouping, Aggregating, Pivoting, and Unpivoting 3.3 Merge Queries, Append Queries, and Relationship Keys 3.4 Query Folding and Load Performance

5 Chapter 4: Design and Implement a Data Model

4.1 Star Schema Design 4.2 Relationships, Cardinality, and Cross-Filter Direction 4.3 Table and Column Properties 4.4 Calculated Columns and Calculated Tables

6 Chapter 5: Create Model Calculations with DAX

5.1 DAX Fundamentals and Measures 5.2 The CALCULATE Function 5.3 Time Intelligence Measures 5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups 5.5 Essential DAX Patterns and Functions Reference

7 Chapter 6: Optimize Model Performance

6.1 Performance Analyzer and DAX Query View 6.2 Model Size Reduction and Cardinality Management 6.3 DAX Optimization Patterns

8 Chapter 7: Create Reports

7.1 Visual Types and Selection 7.2 Visual Formatting and Conditional Formatting 7.3 Copilot in Power BI Reports 7.4 Report Page Configuration and Slicers

9 Chapter 8: Enhance Reports for Usability and Storytelling

8.1 Bookmarks, Navigation, and Drillthrough 8.2 Sorting, Selection Pane, Mobile Layout, and Accessibility 8.3 Export Settings and Automatic Page Refresh

10 Chapter 9: Identify Patterns and Trends

9.1 The Analyze Feature and AI Visuals 9.2 Using Copilot to Summarize the Semantic Model 9.3 Advanced Analytics Patterns

11 Chapter 10: Create and Manage Workspaces and Assets

10.1 Workspaces, Apps, and Content Distribution 10.2 Subscriptions, Alerts, and Semantic Model Refresh 10.3 Dashboards and Advanced Distribution

12 Chapter 11: Secure and Govern Power BI Items

11.1 Row-Level Security (RLS) 11.2 Sensitivity Labels, Item Access, and Governance 11.3 Deployment Pipelines and Content Lifecycle

2.3 Data Types and Type Detection

Key Takeaways Explain This Topic AI

Power BI supports data types including Text, Whole Number, Decimal Number, Fixed Decimal, Date, Date/Time, Time, True/False, and Binary.

Automatic type detection runs on the first 200 rows and can produce incorrect assignments — always verify.

Decimal Number uses 64-bit floating point; Fixed Decimal Number uses exact decimal with 4 decimal places (ideal for currency).

Setting the correct data type impacts storage efficiency, sort behavior, aggregation defaults, and relationship creation.

The 'Using Locale' option allows parsing dates and numbers with regional formatting differences.

Last updated: June 2026

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

  • Data Type Description Storage Size Example
  • Text Character strings of any length Variable "Hello World"
  • Whole Number 64-bit integer (no decimals) 8 bytes 42, -100, 0
  • Decimal Number 64-bit floating point 8 bytes 3.14159
  • Fixed Decimal Exact decimal, 4 decimal places 8 bytes 19.99
  • Date Date only (no time) 8 bytes 2026-03-31
  • Date/Time Date with time component 8 bytes 2026-03-31 14:30:00
  • Date/Time/Timezone Date/Time with timezone offset 8 bytes 2026-03-31 14:30:00 +05:00
  • Time Time only (no date) 8 bytes 14:30:00
  • True/False Boolean value 1 byte TRUE, FALSE
  • Binary Binary data (images, files) Variable (binary content)
  • Duration Time span 8 bytes 2.05:30:00
  • Percentage Decimal displayed as % 8 bytes 0.25 (shown as 25%)
  • 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
  • Power Query scans the first 200 values in each column
  • It attempts to identify the most appropriate type
  • A "Changed Type" step is automatically added to the query
  • If a column has mixed types in the first 200 rows, Text is assigned
  • Problems with Auto-Detection

Problem Example Solution

First 200 rows unrepresentative All nulls in first 200 rows, data starts at row 201 Profile entire dataset, manually set type

  • ZIP codes become numbers "07094" becomes 7094 (loses leading zero) Change type to Text
  • Dates misinterpreted "01/02/2026" — Jan 2 or Feb 1? Use "Using Locale" with correct region
  • Currency as Decimal $19.99 stored as 19.989999... Use Fixed Decimal Number
  • IDs become numbers ProductID "00123" becomes 123 Change type to Text before other steps
  • 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 (not exactly 0.3) 0.1 + 0.2 = 0.30000000000000004 \text{ (not exactly 0.3)} 0.1 + 0.2 = 0.30000000000000004 (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
  • Aspect Impact of Data Type
  • Storage size Integer types compress better than text
  • Sort behavior "1", "10", "2" (text sort) vs. 1, 2, 10 (numeric sort)
  • Aggregation defaults Numeric → Sum; Text → Count; Date → Earliest/Latest
  • Relationship creation Both columns must have the same data type
  • Filter behavior Numeric slicers show range; Text shows dropdown
  • DAX calculations Wrong types cause implicit conversions or errors
  • Best Practices for Type Management
  • Set types in the first step after connecting to data (before other transforms)
  • Delete the auto-generated "Changed Type" step and create your own explicit one
  • Use Fixed Decimal for all currency/monetary fields
  • Keep identifier columns as Text (ZIP codes, product codes, employee IDs)
  • Use Date (not Date/Time) when time component is not needed — reduces storage
  • 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.

  • Symptom Root cause Fix
  • Leading zeros disappear ( 07094 → 7094 ) Auto-typed as Whole Number Set Text before any other step
  • Dates off by months Locale mismatch (DD/MM vs MM/DD) Change Type → Using Locale
  • Currency totals a cent off Decimal (floating point) Use Fixed Decimal Number

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