Skip to content

MODULE 2 · LESSON 11 · Working with tables

Data types and formatting

Separate stored values from their display and prevent silent changes to dates, identifiers and percentages.

By Laurence Svekis · Self-paced · All worked data is synthetic unless you supply your own source.

Try the practice ↓

PredictInspectCalculateCheckExplainTransfer

Your starting point

Prerequisites: Use the ideas and vocabulary from lesson 10; revisit its worked examples if needed.

Definition: A data type determines how a value is interpreted. Formatting determines how it is displayed. Parsing converts text into a typed value according to rules. A unit describes the quantity measured and must remain consistent across comparisons.

Make parsing rules explicit

A date such as 03/04 can mean March fourth or April third depending on convention. Prefer an unambiguous representation such as 2026-04-03 and document the intended calendar meaning. The workbench recognizes valid ISO dates and otherwise preserves the text. It does not guess a locale-specific date or silently remove currency punctuation from a numerical string.

Protect identifiers and precision

Leading zeros can be part of an identifier. Converting 0007 to a number loses that representation. Likewise, displaying a rate as 12% may correspond to an underlying fraction of 0.12 or a field storing 12 percentage units. Mixing these conventions creates a hundredfold error. Name the unit and distinguish a value from its chosen visual presentation.

Inspect conversions after applying them

Test common values, blanks and edge cases. A value that fails numeric parsing is not the same as missing data, so count both. Do not change every column to numeric merely to make an operation run. If the source mixes meters and centimeters, a valid number still needs a unit transformation. Keep the source string or a traceable conversion note so unexpected results can be investigated.

Two worked examples

A percentage convention

A conversion fraction is 0.12. Multiplying by 100 gives 12 percentage units, displayed as 12%. If a column already stores 12, multiplying it again gives 1200, which is not the intended rate. Inspect the dictionary and one known numerator/denominator pair to determine the storage convention.

Dates and identifiers

The value 2026-02-30 matches a superficial date pattern but is not a valid calendar date. The SKU 0030 is a valid identifier even though it resembles an integer. A good parser checks both structure and meaning where possible, while leaving domain-specific judgments to the analyst.

Where this becomes useful

A team receives exports from two systems. One stores durations in seconds and another in minutes. Matching their column names does not make the quantities comparable; convert to a documented common unit before combining them.

Make an attempt

Load Inventory in the Table Explorer and inspect SKU and stock. Correct their types deliberately, then explain why a type override does not alter the original strings. Use a small CSV with 0.12, 12% and blank to inspect numeric, invalid and missing behavior.

Open the accompanying interactive lab →

Misconception check

Claim: Changing a cell’s display format fixes the underlying measurement.

Inspect the correction

Formatting cannot correct a wrong unit, inaccurate source value or ambiguous identifier. Those require explicit decisions.

Independent application

Write a conversion checklist for a mixed export: identifiers with leading zeros, dates, quantities and rates. Include a known-value test for each field, a policy for invalid entries and a note explaining how stored values differ from their display.

Completion records practice and self-review. It is not certification of statistical competence.