Tutorials › Power BI › Data Types in Power BI

Data Types in Power BI

How to set and fix data types — the most common source of errors in Power BI reports

Incorrect data types are the #1 source of errors in Power BI. If a number column is stored as text, SUM() returns 0. If a date column is stored as text, date filters do not work. Fixing data types in Power Query before loading is essential. Power BI has these main data types: Text, Whole Number, Decimal Number, Fixed Decimal Number (currency), Date, Date/Time, Date/Time/Timezone, Duration, True/False (boolean), and Binary. The rule: set data types in Power Query, not in the data model. Power Query changes apply at load time — data model changes apply after loading, which is less efficient.

Example

Common data type fixes
PROBLEM: Sales column shows as "Text" (numbers stored as text)
FIX:
  Click the "ABC" icon left of column name
  → Select "Whole Number" or "Decimal Number"
  → In dialog: "Replace current conversion" → OK

PROBLEM: Date column shows as "123456" serial number
FIX:
  Click the icon → Date
  If still wrong, try: Transform → Date → Parse

PROBLEM: True/False stored as "Yes"/"No" text
FIX:
  Transform → Replace Values: "Yes" → true
  Transform → Replace Values: "No" → false
  Then change type → True/False

COMMON TYPES AND WHEN TO USE:
  Text       → Product names, IDs, categories
  Whole No.  → Count, quantity, year
  Decimal    → Prices, percentages, ratios
  Date       → Order date, invoice date (no time needed)
  Date/Time  → Timestamps, login times
💡 The small icon to the left of each column name shows its current data type. A123 = Text, 123 = Number, 📅 = Date.

Key Points

  • ✓Set data types in Power Query — not in the Report view
  • ✓Wrong data type = wrong calculation results (silent errors are the worst kind)
  • ✓Date columns must be Date type for time intelligence DAX functions to work
  • ✓Currency should be Decimal or Fixed Decimal — not Whole Number
  • ✓Text IDs (like "001", "002") should stay as Text — converting to number loses leading zeros

Practice Question

A column containing sales amounts (like 45000.50) is showing as "Text" data type in Power Query. What happens if you use SUM() on this column in a measure?