Power BI Lesson 9 – Data Types | Dataplexa

Beginner Level · Lesson 9

Data Types

Every column in your data has a type — number, text, date, true/false — and Power BI uses that type to decide what operations are allowed, how values display, and whether aggregations work. Getting data types right before you build a single visual is not optional — wrong types silently break calculations and charts in ways that are hard to diagnose later.

Why Data Types Matter

Think of data types as rules that Power BI enforces about what a column is allowed to contain and do. A column typed as Whole Number cannot hold text. A column typed as Text cannot be summed. A column typed as Date enables a drill-down from Year → Quarter → Month → Day in charts. The type is not just cosmetic — it changes what Power BI can do with the data entirely.

Revenue column typed as Text

Power BI cannot SUM it. Dragging it into a chart shows COUNT instead of total. No DAX aggregation works. You see numbers in the table but they behave like labels.

OrderDate column typed as Text

No time intelligence. No drill-down. Date slicers show a text list instead of a calendar. DATEADD and SAMEPERIODLASTYEAR DAX functions all fail. Sorting alphabetically gives the wrong order.

Revenue typed as Decimal Number

Power BI SUMs it automatically. All DAX aggregations work. It shows the ∑ icon in the Data pane. You can apply currency formatting. Conditional formatting and data bars work correctly.

Every Data Type in Power BI

Power BI supports nine data types. Each has an icon that appears next to the column name in the Data pane and in Power Query Editor. Learning these icons means you can diagnose type problems at a glance without opening any dialogs.

Icon	Type Name	What it stores	Example values
1.2	Decimal Number	Numbers with decimal places — the most flexible numeric type	19.99, 1200.50, -42.5
$	Fixed Decimal Number	Currency values — fixed 4 decimal places, avoids floating point rounding errors	$1,200.0000, $19.9900
123	Whole Number	Integers with no decimal places — IDs, counts, quantities	1, 42, 1001, -5
%	Percentage	Decimal numbers stored as fractions, displayed as percentages	0.25 displays as 25%
📅	Date/Time	A date with a time component — full timestamp	2024-01-05 14:32:00
📅	Date	Date only — no time component. Use this for order dates, birth dates, etc.	2024-01-05
⏱️	Time	Time of day only — no date component	14:32:00
🔤	Text	Any string of characters — names, categories, codes, descriptions	"North", "Laptop Pro", "C101"
✓✗	True/False	Boolean values — only two possible values	TRUE, FALSE

Where to See and Change Data Types

You manage data types in two places — Power Query Editor (before data is loaded into the model) and the Data pane in Report or Data View (after it is loaded). Always fix data types in Power Query Editor rather than after loading — type changes in Power Query are applied during the load step and are more reliable.

Power Query Editor — Orders Table

Home

Transform

Add Column

View

Queries

Orders

Customers

Products

123 OrderID	🔤 ← wrong! OrderDate	🔤 Region	🔤 ← wrong! Amount	123 Quantity
3001	05/01/2024	North	1200	2
3002	07/01/2024	South	350	1
3003	09/01/2024	North	85	5
3004	12/01/2024	East	1200	1

Applied Steps

Source

Navigation

Changed Type ← current

In the mockup above, OrderDate and Amount are both showing the text icon (🔤) and highlighted in red — Power BI misdetected them as Text when it should have detected Date and Decimal Number respectively. This is exactly the situation you will fix in the next section.

How to Change a Data Type in Power Query Editor

There are three ways to change a data type in Power Query Editor. All three do the same thing — they add or update a "Changed Type" step in the Applied Steps panel.

Method 1 — Click the icon

Click the type icon (e.g. 🔤) in the column header directly. A dropdown appears listing all available types. Click the type you want. This is the fastest method for a single column.

Method 2 — Right-click the header

Right-click a column header → Change Type → select the type from the submenu. This gives you the same options as Method 1 but is useful when working in a context menu workflow.

Method 3 — Transform ribbon

Select one or more columns (hold Ctrl to select multiple), then click Transform → Data Type in the ribbon and choose the type. Best method when changing multiple columns at once.

Type dropdown — clicking the 🔤 icon on OrderDate column

📅 Date ✓ correct

📅 Date/Time

⏱️ Time

1.2 Decimal Number

123 Whole Number

🔤 Text

✓✗ True/False

After changing OrderDate to Date type:

123 OrderID	📅 OrderDate	🔤 Region	1.2 Amount
3001	Jan 5, 2024	North	$1,200.00
3002	Jan 7, 2024	South	$350.00
3003	Jan 9, 2024	North	$85.00
3004	Jan 12, 2024	East	$1,200.00

The "Replace Current" vs "Add New Step" Dialog

When you change a data type in Power Query Editor and a "Changed Type" step already exists, Power BI asks whether to replace the existing step or add a new one. This matters because your choice affects the Applied Steps list and how the query is structured.

Change Column Type

The selected column already has a data type change applied. Do you want to replace the existing type change or add a new step?

Replace current conversion

Updates the existing Changed Type step in place. Keeps your Applied Steps list clean. Use this when you made a mistake and want to correct it.

Add new step

Adds a second type change step after the existing one. Use this only if you intentionally want two separate type steps — for example converting to text first, then reformatting before converting to date.

Cancel

The Most Common Data Type Problems

These are the type problems you will encounter most frequently in real-world data, along with exactly how to diagnose and fix each one.

PROBLEM 1: Numbers stored as Text
─────────────────────────────────
Symptom: Revenue column shows 🔤 icon, not ∑
         Dragging to a chart shows Count, not Sum
         SUM([Revenue]) returns an error in DAX

Cause:   CSV exported with currency symbols ($1,200) or commas
         (1,200.00) — Power BI cannot convert "1,200" to a number
         because of the comma formatting

Fix:     In Power Query → select column → Transform →
         Replace Values → replace "," with "" (nothing)
         Then change type to Decimal Number

─────────────────────────────────────────────────

PROBLEM 2: Dates stored as Text
────────────────────────────────
Symptom: OrderDate shows 🔤 icon
         Date slicer shows a text list not a calendar
         YEAR() and MONTH() DAX functions fail

Cause:   Dates are formatted as "05/01/2024" or "Jan 5, 2024"
         Power BI read them as strings

Fix:     Change type to Date directly — Power BI can usually
         parse standard date strings automatically
         If format is unusual (e.g. "20240105") use:
         Transform → Parse → Date

─────────────────────────────────────────────────

PROBLEM 3: Mixed types in a column
───────────────────────────────────
Symptom: Some rows show Error after changing type
         Column has nulls and numbers mixed

Cause:   A few rows contain "N/A", "TBD", or blank strings
         mixed into what should be a numeric column

Fix:     Before changing type, replace problem values:
         Transform → Replace Values → "N/A" → null
         Then change type — nulls are accepted, "N/A" text is not

─────────────────────────────────────────────────

PROBLEM 4: Excel serial number dates (e.g. 45292)
──────────────────────────────────────────────────
Symptom: Date column shows numbers like 44927, 45021 etc.

Cause:   File was exported from Excel with date serial numbers
         instead of formatted dates

Fix:     Change type directly to Date — Power BI recognises
         Excel serial numbers and converts them automatically

Quick diagnosis — what icon should each column have?

Column name	Correct icon	Wrong icon = problem
OrderID, CustomerID, ProductID	123 Whole Number	🔤 Text — can't use in relationships
Revenue, Amount, Price, Cost	1.2 Decimal Number	🔤 Text — SUM fails, chart shows Count
OrderDate, HireDate, BirthDate	📅 Date	🔤 Text — no time intel, no calendar slicer
Quantity, Units, Count	123 Whole Number	1.2 Decimal — shows 2.0 instead of 2
Region, Category, Status, Name	🔤 Text	N/A — text is usually correct here

Decimal Number vs Fixed Decimal Number vs Whole Number

Three of the numeric types are easy to confuse. Choosing the right one for financial data matters because they behave differently under the hood — especially when summing millions of rows.

Type	Precision	Best for	Watch out for
Decimal Number (1.2)	Floating point — up to 15 significant digits	General numeric data, measurements, percentages	Can produce tiny rounding errors in financial sums (e.g. $100.0000000001)
Fixed Decimal Number ($)	Fixed 4 decimal places — no floating point	Currency, financial amounts, accounting data	Slightly slower than Decimal Number — only use when rounding precision matters
Whole Number (123)	64-bit integer — no decimal places at all	IDs, counts, quantities, ranks, years	Any decimal value is truncated — 1.9 becomes 1

Handling Errors After a Type Change

When you change a column's type and some rows contain values that cannot be converted, Power BI marks those cells as Error in red. This is a data quality signal — not a crash. You have three options for dealing with those errors.

Amount column after changing type to Decimal — errors appear

OrderID	Region	1.2 Amount	Status
3001	North	$1,200.00	Shipped
3002	South	Error	Delivered
3003	North	$85.00	Processing
3004	East	Error	Pending
3005	West	$350.00	Shipped

2 errors detected in Amount column — original values were "N/A"

Option 1 — Remove rows with errors

Right-click the column header → Remove Errors. Deletes any row where that column contains an error. Use when error rows are genuinely bad data you do not need.

Risk: you lose entire rows, not just the bad cells

Option 2 — Replace errors with null

Right-click the column header → Replace Errors → enter null. Replaces just the error cells with null, keeping the rest of the row intact.

Best for: keeping rows but treating bad values as missing

Option 3 — Replace errors with zero

Right-click the column → Replace Errors → enter 0. Replaces error cells with zero. Use only if zero is a meaningful replacement for your business context.

Caution: 0 will be included in all aggregations

Teacher's Note: The single most important habit to build in Power BI is this — open every new query in Power Query Editor and check every column header icon before you click Close and Apply. Spend two minutes confirming that dates show 📅, numbers show 1.2 or 123, and text shows 🔤. Those two minutes prevent hours of debugging wrong totals, broken DAX, and charts that show Count when they should show Sum.

Practice

Practice 1 of 3

If a Revenue column shows the 🔤 icon instead of ∑, dragging it into a chart will show ___ instead of Sum.

Practice 2 of 3

When changing a data type causes some cells to show "Error" in red, you can right-click the column and choose Replace ___ to swap the error cells with null while keeping the rest of the row.

Practice 3 of 3

For financial currency columns like Revenue and Price, the most precise numeric type to use is ___ Decimal Number, because it uses fixed 4 decimal places and avoids floating point rounding errors.

Lesson Quiz

Quiz 1 of 3

You load a CSV file and notice the OrderDate column is showing a text icon. You change its type to Date and some rows immediately show "Error." What is the most likely cause?

The CSV file is corrupt and needs to be re-exported from the source system Some rows contain values that cannot be parsed as dates — for example "TBD", "N/A", or a different date format Power BI does not support dates from CSV files — you must use Excel The Date type is only available in the DAX Query View, not in Power Query Editor

Quiz 2 of 3

You have a Quantity column typed as Decimal Number showing values like 2.0, 5.0, and 1.0. The business wants clean integers. Which type change should you make?

Change to Fixed Decimal Number — it rounds to 4 decimal places Change to Text — then format as a number in the report Change to Whole Number — it removes all decimal places and stores only integers Change to Percentage — it will display without decimal places

Quiz 3 of 3

A colleague says their Revenue column is showing the right numbers in the table but the bar chart is showing Count of Revenue instead of Sum of Revenue. Without looking at their file, what is almost certainly wrong?

The Revenue column data type is Text — Power BI cannot aggregate text columns with SUM so it defaults to Count The chart type is wrong — they should be using a clustered column chart instead of a bar chart The Revenue column needs to be added to the Filters pane before it can be summed There is a DAX measure overriding the default aggregation behaviour

Next up — Lesson 10 walks you through Basic Transformations in Power Query Editor — removing duplicates, filtering rows, renaming columns, trimming whitespace, and all the everyday cleaning steps that every real-world dataset needs before it is ready to report on.

← Previous Course Index Next →

Power BI Course