A city-data file can contain strings that look numeric but function as labels: district identifiers, postal codes or record references. If a spreadsheet turns a fictional code00007 into7, arithmetic value may survive while the identifier no longer matches the source. Preserve the original download and decide which columns are text before relying on an imported sheet. The example here is invented, not a reported error in a real city dataset.
| Original fictional field | Risky interpretation | Preservation question |
|---|---|---|
| district_id = 00007 | Numeric7 | Does the imported text retain all five original characters? |
| Long record identifier | Numeric conversion beyond precision limits | Does the exact character string still match the source? |
Why the convenient conversion can be the wrong one
Microsoft explains that Excel can remove leading zeros and convert long numeric-looking entries, including codes, when treating them as numbers. It documents a15-significant-digit numeric precision limit and recommends text for sufficiently long number codes. That is a reason to distinguish an identifier from a quantity you intend to calculate.
In the fictional example, district_id contains00007. Treating it as text preserves those five characters; interpreting it numerically makes7 a different representation. Do not claim that adding four zeros later recovers the source unless the original file or authoritative specification actually establishes that exact code.
Make the preservation choice during import
Microsoft’s documented Power Query import route allows individual columns to be assigned the Text type. Follow the instructions for your actual Excel version and inspect the transformation/import result against the untouched source. Menus and automatic-conversion controls vary by version, so a guide for one edition should not be presented as every user’s identical screen.
For an original import note, retain the source filename/link, relevant column names, chosen types and several actual before/after samples. Check leading zeros and long identifiers, not only whether the sheet looks tidy. If a conversion has already changed a value, return to the original source rather than guessing the missing characters.
Formatting is not a substitute for source truth
Microsoft distinguishes display formatting from treating data as text and warns that later formatting does not restore values already changed in the ways described. A cell can look plausible without proving that its underlying identifier still matches the download. Keep presentation choices separate from data-preservation checks.
Before paying to print a directory, map or report, verify that the identifiers still link to the intended records. Keep the original file as the comparison point when exporting or sharing the result. No spreadsheet or city file was tested for this article; the practical output is a reproducible preservation check, not an invented assurance that a published dataset is correct.
Sources & further reading
Published sources for the details and examples discussed in this story.
