Power BI / Basic: analytical and modeling foundations / Lesson 2

Power Query types and locale

Preserve identifiers, numeric meaning and dates during preparation rather than trusting automatic detection or display formatting.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Separate source text, conversion and model formatting.
Choose explicit data types and locale assumptions.
Treat conversion errors as evidence requiring a quality decision.

Keep identifiers as identifiers

A numeric-looking product code can be text when its leading zeros or exact spelling matter. Converting0017 into17 destroys identity information even if a visual later displays four digits. Quantities, prices and dates have different contracts. Power Query types guide transformations before loading; model format strings guide presentation later and do not reverse a destructive source conversion. Start with a column specification naming meaning, type, null policy and acceptable range. Automatic type detection is convenient for exploration but should be reviewed before a repeatable model relies on it.

Make locale explicit

Text dates such as01/02/2026 can mean different days under different locales. A decimal comma can similarly change how numeric text is parsed. The first M expression builds a tiny table and converts its date column using an explicit day-first locale. Its intended date is2January2026. This complete expression is displayed but not run in a Power Query engine. Choosing a locale does not fix mixed conventions within one source; those rows need a documented source-quality rule rather than a conversion that guesses independently for each value.

powerquery Explicit types and locale in M

let
    Source = #table(type table [Code = text, Day = text], {{"0017", "02/01/2026"}}),
    Typed = Table.TransformColumnTypes(Source, {{"Code", type text}, {"Day", type date}}, "en-GB")
in
    Typed

The intended row retains Code0017 as text and converts Day to2January2026. This M example is unexecuted.

Choose precision deliberately

Whole numbers suit counts with an integer contract. Decimal Number uses binary floating-point and cannot represent every decimal fraction exactly. Fixed Decimal Number has a fixed four-decimal scale and can suit monetary inputs within its supported range, but it does not decide business rounding rules or currency conversion. The second Python program uses Decimal for a tiny exact monetary calculation. It is an independent oracle, not a claim that Python and every DAX type behave identically. Document whether rounding happens per line, per invoice or only in display, because those choices can change totals.

python Exact synthetic decimal oracle

from decimal import Decimal
amount = Decimal("0.10") + Decimal("0.20")
print(amount)
print(amount == Decimal("0.30"))

Expected output

0.30
True

0.30 and True follow Decimal string inputs. No workbook, model or external source is read.

Retain errors and null meaning

An invalid date conversion should not automatically become today’s date or a silent null. A null can mean missing, not applicable or unknown, whereas zero is an actual numeric value. Decide whether errors should stop refresh, be quarantined with an audit count or follow another documented rule. Keep enough synthetic examples to explain the failure without exposing source data. Replacing every error with zero may make a chart look complete while understating revenue or hiding an upstream format change. Quality decisions belong to the data contract, not a cosmetic cleanup step.

Review the final query output

Inspect the final prepared schema, not only the first preview step. Later merges, unpivoting or custom columns can introduce new type or null behavior. Preview sampling can miss malformed rows beyond the sampled range, so profile the intended scope and retain counts of accepted and rejected rows. This course executes no connector, refresh or Power Query engine. The M expression stays non-runnable and the Decimal program establishes one exact arithmetic result. A future authorized model check must verify actual conversion outcomes and row coverage before relying on a successful-looking preview.

Common errors and fixes

Product code0017 becomes17

Why it happens: An identifier was automatically converted to a number.
How to fix it: Preserve the source identifier as text before information is lost.
Prevent it next time: Define identifier types explicitly in the preparation contract.

A date flips between January and February

Why it happens: Ambiguous text was parsed under an unstated locale.
How to fix it: Apply the documented source locale and reject mixed-format exceptions.
Prevent it next time: Keep an unambiguous date fixture in quality review.

All conversion errors are replaced with zero

Why it happens: A quality failure was hidden to complete the visual.
How to fix it: Classify invalid, missing and actual zero values separately.
Prevent it next time: Publish rejection counts and agreed error handling.

Check your understanding

1. Why does formatting17 as0000 not recover the original identifier reliably?

Show worked solution

Formatting changes display only and assumes a width that may not match the source identity. Distinct source strings can already have collapsed to one number. Retain the original text and define any normalization rule explicitly; do not reconstruct business keys from presentation after destructive conversion.

2. What must be decided before replacing a failed price conversion with null?