Prepare data with an auditable correction log

Understand the method

Preparation should produce a usable analytical table and an explanation of how it was created. Keep the original file immutable. Work on a separate copy, record changes against stable order identifiers and retain an issue log. If you sort a spreadsheet, sort the whole table so identifiers remain attached to their attributes. Colouring cells alone is not a durable audit trail, especially after exporting to CSV.

Resolve duplicate groups before replacing values

A004 appears twice with identical field values. For this supplied order-level contract, one analytical record is appropriate. Retain one identical copy and record the removal of the excess export row. This rule depends on the key being a genuine order identifier. Two same-value orders with different identifiers are not duplicates and must both remain.

Y007 has two rows with the same identifier but different channels. “Keep first” or “keep last” would make the result depend on file ordering. Neither record carries a trusted sequence or update timestamp. Use the verified reference, which confirms store, and retain the matching record in the analytical table. Preserve the alternative in the issue evidence. If no authority were supplied, the conflict would remain unresolved; the fact that a spreadsheet offers a Remove Duplicates button would not settle it.

Use explicit correction authority

Trim whitespace and convert the declared channel representation to lowercase for A005. This is a representation rule, not an inference about behaviour. Correct A008’s impossible 31 April date to 8 April using the supplied reference. Restore A009’s value to €110, Y003’s channel to store, Y005’s value to €190 and Y006’s duration to four days only after checking those exact fields in the reference. Keep the source value and action in the log.

Do not globally convert blanks to zeros. A missing delivery duration has no authoritative replacement in the reference. Leave it blank and retain the order for the sales population when its required financial fields are verified. The preparation contract can support different eligible populations for different measures, as long as you name them. One universal “complete cases only” filter would silently remove valid completed sales.

Reconcile what changed

Begin with 46 raw records. Removing one exact excess row and one conflicting excess row leaves 44 analytical orders. All 44 business IDs remain represented. Field corrections change values without changing this count. The two months each contain 22 orders across all statuses. Filtering to completed orders leaves 40 for sales and value-distribution analysis. Keep excluded returned and cancelled orders visible in a separate count.

Re-run key uniqueness, real-date parsing, allowed-channel checks, required-value tests and delivery-type checks on your prepared copy. Compare fields against the verified file by order_id, not by row position. A clean count of 44 alone cannot prove that the retained Y007 record or the restored Y005 amount is correct. Reconciliation should include identifiers and relevant values as well as totals.

Plan for a changed source

A reproducible process should expose a new duplicate, a new missing amount or a previously unseen category. Do not hardcode the expected 44 rows as a substitute for validation. Expected fixture counts are useful learning checkpoints; real inputs require their own scope and exception review. When a rule fails, stop the affected summary, record the failure and seek evidence rather than forcing the output to match an answer key.