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?

Related Topics

Power Query BasicsClean, filter and reshape data in Power Query Editor before loading into Power BIConnecting to Data in Power BIImport data from Excel, CSV, SQL Server, and web sources into Power BI