Guide to understanding, detecting, and correctly converting data types in Power Query / Power BI. Covers why types matter, how Power Query guesses types, a cheat-sheet of common types, common pitfalls (locale, empty strings, mixed types), best practice ordering of Applied Steps, useful M patterns, and debugging tips.
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, co...
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 : Re...
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 th...
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 cal...
Pitfalls and how to survive them 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 > Usin...
Best practice order (Applied Steps matters!) Connect to source (we covered this). Promote headers (if needed). Remove columns you don't need (less work downstream). Clean text: Trim, Clean, Remove non-printing characters. Fix known problems: Replace errors, empty strings -> null. ...
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 co...
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 of...
10 study modes available based on your content