Power Query types: protect identifiers with leading zeros

An identifier that contains digits is not necessarily a number. Customer ID 0012 may need to remain exactly 0012 through import, transformation, lookup and export. Converting it to numeric 12 removes information that a later text conversion cannot reliably reconstruct.

Set identifier columns to text before a conversion discards their original representation. Display formatting and data type solve different problems: a number format can make 12 look like 0012 while the underlying value remains numeric 12.

Start from a source that still contains the zeros

Use raw_lines.csv from the spreadsheet quality lab. Its CustomerID values include 0012, 0042, 0789 and 0099. The file stores those characters explicitly.

Import through Excel's text/CSV workflow and choose to transform the data before loading it. Inspect the automatically applied steps. If a Changed Type step converts CustomerID to a number, remove or revise that step before it changes the original text.

An illustrative M step after a correctly parsed header table is:

= Table.TransformColumnTypes(#"Promoted Headers", {{"CustomerID", type text}, {"InvoiceID", type text}, {"LineID", type text}})

The preceding step name must match your actual query. This line documents the identifier types; it is not a complete file connector. The other fields still need explicit types and validation according to their own meanings.

Microsoft describes type conversion and locale effects in its Power Query data-type guidance.

Why changing back to text may be too late

If an earlier step converted 0012 to numeric 12, converting that result to text produces 12. The original width is gone. A four-character padding rule could recover 0012 only if the source contract guarantees that every ID has exactly four characters.

Without that contract, padding may invent an identifier. Distinct source IDs 12 and 0012 could represent different entities, and a numeric conversion would collapse them. Returning to the unmodified source is the safer recovery when the original representation still exists.

The workbook supplied with the lab stores CustomerID as text and structurally checks that Clean_Lines!D2 remains 0012. That is a file-generation check; Power Query refresh must still be reviewed in Excel.

Apply the same rule to reference tables

Preserving zeros only in the sales table is insufficient if the customer reference table has already converted its keys to numbers. Align types and normalization rules on both sides before merging or looking up values.

The fixture also contains a separate problem: customer 0042 appears twice with conflicting regions. Correct text typing preserves that ambiguity; it does not solve it. Type checks, key-uniqueness checks and unmatched-record checks address different defects.

Use the duplicate-key lookup lesson to distinguish these cases rather than interpreting every failed lookup as a leading-zero issue.

Validate more than the visible cell

Inspect distinct source and output keys, string lengths and a sample of leading-zero values. Confirm that the clean sales table contains four distinct CustomerID values and that none has been shortened. Check exported CSV text as well as worksheet display when the data will move to another system.

If a downstream application automatically infers types again, it can undo the protection. Document import types at each handoff rather than assuming a CSV carries a formal text-column schema.

For dates and decimal amounts, use the appropriate locale during conversion. The string 01/02/2026 is ambiguous without a format contract, while a customer identifier should not be parsed using numeric or date rules at all.

Make refresh preserve the contract

Add a temporary new customer ID 0007 to a source copy, refresh and verify that all four characters survive. Then add an unexpected alphanumeric ID such as A007. A text column should preserve it; a rule that assumed identifiers were integers would fail or corrupt the value.

Decide whether alphanumeric IDs are valid under the source contract. Preserving the raw value is separate from accepting it as a valid business identifier. Invalid values should produce an exception with the original text intact.

Exercise: create two source rows with IDs 12 and 0012. Demonstrate how numeric conversion merges their representation, then rebuild the import so both remain distinct. Explain why four-digit formatting alone cannot prove that the underlying keys survived.

NeuraPath's Data Analytics with Generative AI course develops Excel and Power Query skills alongside data-quality reasoning. A reliable import workflow preserves identity through refresh and makes its type assumptions visible.

Continue learning

This article is part of the Excel and spreadsheet quality sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Pivot tables: reconcile totals before sharing a dashboard.
• Continue with Combine monthly Excel files with a schema check.