Keep original values when creating normalized analysis columns

Use two layers with different jobs

Keep the raw data alongside the transformation so a later question does not require repeating the extraction.

Test an invoice with the dates, quantities or amounts you find hardest to copy accurately. ParseMill puts extracted invoice fields beside the source for review and correction before export. Open the downloaded workbook in your usual spreadsheet application and check those values there too.

A normalized value can be useful without being source text. Label it accordingly, and record the rule, units, reviewer and input version needed to reproduce it. Do not rely on a spreadsheet formula alone to explain its purpose.

Copy a two-layer worksheet pattern

These synthetic examples show that normalization need not produce a number for every row. R3 remains a qualified result because a scalar would require an additional analytical choice. Keep that decision visible instead of treating an empty derived value as a failed transcription.

W3C: PROV Data Model distinguishes source entities, derivation activities and responsible agents. This provides a useful conceptual basis for the raw-to-derived relationship in your worksheet.

Make transformations reproducible

1. Assign stable row identifiers and retain the source version and location with the raw fields. Freeze a reviewed raw snapshot before analysis begins.
2. Name each derived field by quantity and representation. Record the transformation formula or rule version and the conditions under which it applies.
3. Test the rule on explicit expected examples, including unsupported or ambiguous inputs. Keep those cases in an exception list rather than forcing a value.
4. After a raw correction, identify and recompute affected derived fields. Record the new analysis revision and repeat the relevant checks.

Microsoft: Table.TransformColumnTypes applies declared column conversions. Put such operations in the derived layer when they interpret source strings, and keep the raw text available for comparison.

Prevent accidental overwrites

A copied paste, fill-down or cleanup command can modify raw and derived columns together. Protect or separate the reviewed raw snapshot using the tools appropriate to your workflow, and compare it with the saved version before release. A naming convention alone is helpful but does not enforce immutability.

Do not hide transformation failures by replacing them with zero. Keep the raw input, error category and action needed so the analyst can distinguish unsupported notation from an actual zero result.

Verify the relationship, not only the answer

For the fixture, a reviewer should reproduce 0.125 and 1.25 from the stated rules and confirm why R3 has no scalar. Save the rule record with the export so another person can repeat the analysis after a source revision.

For invoice work, review ParseMill’s extracted values beside the source before adding your own analysis columns to the downloaded workbook. Count that downstream preparation in the buying test. The transformation rules in this guide remain your documented analysis step.