Keep a measured zero distinct from a missing value

Give absence its own state

Keep that distinction in the workbook so missing evidence does not silently become a reassuring number in the next calculation.

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.

The state vocabulary should describe evidence rather than guessed causes. A blank might mean not tested, unavailable or a printing omission, but those meanings require a source legend or clarification. Until then, use a neutral state such as blank in source.

Copy this missing-state fixture

The fixture’s Empty cell wording is a worksheet description, not text inserted into the original source. Preserve that distinction in your own export. A numeric null can accompany several states, so the state column carries information that the numeric column alone cannot express.

Microsoft: M language values defines null as absent or indeterminate information. It is distinct from the numeric value zero.

Apply the state rule

1. Inspect the source cell and any legend or footnote. Record whether the cell is visibly empty, contains a mark or explicitly states a result.
2. Assign a state supported by that evidence. Keep the raw mark even when its meaning has been established.
3. Populate the numeric field only for a reported numeric observation that is suitable for conversion. Leave the reason for absence in the state field.
4. Check formulas, summaries and exports for default replacements of null or blank with zero. Review denominators as well as totals.

Microsoft: Power Query data types describes field-level data types. A numeric field is useful for arithmetic, but a separate text state is needed when the source distinguishes several kinds of nonnumeric result.

Watch downstream calculations

Replacing two missing observations with zero can lower an average and inflate the apparent number of measured samples. Omitting them without reporting the missing count can also mislead. State how many records have numeric results and how many fall into each missing category before interpreting a summary.

Do not decide that a dash means zero because most neighboring results are zero. If a legend defines the dash, apply that definition only within its documented scope. Different reports from the same supplier may use different legends.

Verify both values and states

For this fixture, only the first row contributes a reported numeric zero. The other three remain nonnumeric, with different explanations visible. A successful handoff preserves all four records and their source references rather than dropping the inconvenient rows.

Test an invoice containing a printed zero and a genuinely missing required value. In ParseMill, check the source, the extracted field and any validation issue before exporting. The laboratory state model above is a manual review pattern, not a promised set of automatically generated invoice fields.