Data Type Detection and Conversion — The Great Identity Crisis in Your Columns

"Power BI thinks my dates are text and my numbers are feelings." — the cry of someone who forgot to set types

Welcome back, brave data wrangler. You've already seen how to open the Power Query Editor, connect to data sources, and watch the magical list of Applied Steps grow like a bonsai forest. Now we tackle the part where your columns decide who they want to be when they grow up: data types. This topic builds on Power Query Editor fundamentals and the importance of Applied Steps: where you put the "Change Type" step matters as much as whether you promoted headers correctly or removed junk columns earlier.

---

Why data types actually matter (yes, more than style points)

• Calculations: You can't sum a column of "42" strings. Power BI will sulk and refuse to do math.
• Visuals & Aggregations: Date hierarchies, numeric axes, and sorting depend on correct types.
• Model relationships: Relationships based on keys need compatible types or Power BI starts ghosting your joins.
• Performance & Storage: Using the right numeric or integer type reduces model size.

Imagine a spreadsheet where your salary column is typed as text. You ask for total payroll and get... crickets. That is the sorrow of mis-typed data.

---

How Power Query detects types (and where it trips)

• Auto-detection: When you load data Power Query often inserts a Changed Type step automatically (or you can manually invoke Home > Detect Data Type). This attempts to guess each column's type.
• Visual cues: Look at the small type icon in the column header (ABC, 123, calendar, clock, true/false). Click it to change the type from the UI.
• Applied Steps: The type change is recorded as a step (e.g., "Changed Type") and you can reorder, delete, or edit it.

Why people keep misunderstanding this: auto-detection is a guess, not revelation. It works often, but locale settings, mixed values, and invisible whitespace make it fail spectacularly.

---

The common types cheat-sheet

Icon / Example	Power Query Type	When to use it
ABC	type text	IDs, names, codes with leading zeros
123	type number / Int64.Type / type decimal	Quantities, measures, numeric calculations
$	Currency	Financial figures with currency semantics
calendar	type date	Date-only (no time)
datetime	type datetime	Date + time
timezone	type datetimezone	Date + time + timezone offset
clock	type time	Time-of-day values
duration	type duration	Time spans (3 days, 02:00:00)
true/false	type logical	Binary flags
binary	type binary	Files, images, attachments

---

Pitfalls and how to survive them

1. Locale problems

   • CSVs or text files from different regions often use , as decimal separators or dd/MM/yyyy date formats. Auto-detect will use the global/culture settings and might misinterpret.
   • Fix: Use Change Type with Locale (Transform > Data Type > Using Locale) or in M: Table.TransformColumnTypes(Source, {{"DateColumn", type date}}, "de-DE").

2. Empty strings vs nulls

   • "" (empty text) is not the same as null. Many conversions fail on empty strings.
   • Fix: Replace empty strings with null before type conversion: Table.ReplaceValue(..., "", null, Replacer.ReplaceValue, {"Col"})

3. Mixed-type columns

   • If a column has a few stray text values among numbers, auto-change will create errors.
   • Fix: Clean (Trim, Replace Values) or coerce with try/otherwise before forcing a type.

4. Leading/trailing spaces and invisible characters

   • " 123" or non-breaking spaces break conversions.
   • Fix: Transform > Format > Trim/Clean or use Text.Trim and Text.Clean first.

5. DateTime vs DateTimeZone

   • If source includes timezone, use datetimezone; otherwise you may lose offset information.

---

Best practice order (Applied Steps matters!)

1. Connect to source (we covered this).
2. Promote headers (if needed).
3. Remove columns you don't need (less work downstream).
4. Clean text: Trim, Clean, Remove non-printing characters.
5. Fix known problems: Replace errors, empty strings -> null.
6. THEN change data types.

Why this order? If you let Power Query auto-change types before cleaning, the "Changed Type" step will be full of errors and you'll spend time chasing red exclamation marks. Keep the type-step later if you expect messy data.

---

Hands-on: Useful M patterns (copy-paste ready)

Simple explicit change of types:

// Set Sales to 64-bit integer and OrderDate to date
Table.TransformColumnTypes(Source, {{"Sales", Int64.Type}, {"OrderDate", type date}})

Change type with a locale (useful for dd/MM/yyyy or comma decimals):

// Interpret OrderDate using British format
Table.TransformColumnTypes(Source, {{"OrderDate", type date}}, "en-GB")

Safer conversion that avoids errors (coerce with try/otherwise):

Table.TransformColumns( 
    Source, 
    { {"Amount", each try Number.FromText(_) otherwise null, type number} } 
)

Use specific converters for complex parsing:

// Convert a Date string using Date.FromText with culture
Table.TransformColumns(
  Source,
  { {"DateText", each Date.FromText(_, "en-GB"), type date} }
)

If you want to add a cleaned, converted column instead of overwriting:

Table.AddColumn(Source, "Amount_num", each try Number.FromText([Amount], "de-DE") otherwise null, type number)

---

Quick debugging checklist when conversions fail

• Look at the column header icon — what type does Power Query think it is?
• Click the gear icon on the "Changed Type" step to see the exact conversions Power Query applied.
• Filter the column for errors (click the error indicator) to see offending rows.
• Use a temporary column with try/otherwise to surface problematic values.

---

Final thoughts (mic drop)

• Types are contracts between your data and Power BI — if the contract is wrong, everything downstream behaves badly.
• Clean before you coerce. Use locale-aware conversions when needed. Use try/otherwise to fail gracefully.

Key takeaways:

• Auto-detection helps, but never blindly trust it.
• Use Table.TransformColumnTypes for bulk changes and locale-aware conversions.
• Use Table.TransformColumns with try/otherwise when data is messy.
• Place type conversions in a sensible spot in your Applied Steps (after cleaning, before heavy modeling).

Imagine shipping a dataset that treats ZIP codes as numbers, stripping leading zeros — that tiny mistake costs hours and a whole lot of pain. Correct typing is your insurance policy against that disaster.

Go forth, convert wisely, and may your types be exact and your errors be few. If something explodes, remember: the Applied Steps pane is reversible — click the gear, edit the step, and be the hero your report needs.