Use lessons 01–01 or equivalent skills before this exercise. You can revisit them from the lesson list.
A spreadsheet or calculator; optional local Python. Text-only AI can explain a plan but cannot establish that calculations ran.
Start with the idea
Clean data with a record of what you changed and why.
Use the supplied key and duplicate rule. Preserve unresolved records. Report how much of the data the result actually covers.
Cleaning log: A record of accepted, changed, excluded and unresolved rows with reasons.
See the example and check your understanding
A made-up teaching example
R1 with amount ten appears twice. R2 has fifteen; R3 is missing its amount. The declared rule treats the identical repeated R1 ID as a duplicate. The known accepted total is twenty-five.
Ask yourself
Does that known total establish the full total including R3?
Show the explanation
No. R3 remains unresolved. Do not treat a repeated item description as a duplicate ID without a rule.
Start with these checks
- Use the declared key.
- Keep missing and disputed rows.
- State incomplete coverage.
Use the different practice case below and check every requirement in its full instructions.
Full learning objectives
- Detect duplicates and invalid values.
- Preserve originals and transformation rules.
- Avoid unsupported joins and imputations.
Captions appear here during playback.
Clean data without hiding your decisions
Clean data with a record of what you changed and why.
Video playback is unavailable. Read the descriptive transcript below, go directly to practice, or open the video file.
Source-based teaching example · Fictional data · Reviewed 2026-10-03. Microsoft Mark · locally synthesized. On a small screen, use fullscreen for diagram details or read the transcript.
Read the descriptive transcript
0:00 · What you will learn
Clean data with a record of what you changed and why.
What appears on screen
Clean data with a record of what you changed and why.
0:05 · The idea
Use the supplied key and duplicate rule. Preserve unresolved records. Report how much of the data the result actually covers.
What appears on screen
Use the supplied key and duplicate rule. Preserve unresolved records. Report how much of the data the result actually covers.
0:17 · One useful term
Cleaning log. A record of accepted, changed, excluded and unresolved rows with reasons.
What appears on screen
A record of accepted, changed, excluded and unresolved rows with reasons.
0:27 · A worked example · 1
R1 with amount ten appears twice. R2 has fifteen; R3 is missing its amount. The declared rule treats the identical repeated R1 ID as a duplicate.
What appears on screen
R1 with amount ten appears twice. R2 has fifteen; R3 is missing its amount. The declared rule treats the identical repeated R1 ID as a duplicate.
0:41 · A worked example · 2
The known accepted total is twenty-five.
What appears on screen
The known accepted total is twenty-five.
0:47 · Your turn to think
Does that known total establish the full total including R3? Pause and decide before continuing.
What appears on screen
Does that known total establish the full total including R3? Pause the video if you need more time.
1:04 · Why this answer works
No. R3 remains unresolved. Do not treat a repeated item description as a duplicate ID without a rule.
What appears on screen
No. R3 remains unresolved. Do not treat a repeated item description as a duplicate ID without a rule.
1:15 · Check 1
Use the declared key.
What appears on screen
Use the declared key.
1:19 · Check 2
Keep missing and disputed rows.
What appears on screen
Keep missing and disputed rows.
1:24 · Check 3
State incomplete coverage.
What appears on screen
State incomplete coverage.
1:29 · Now try a different case
Open the lesson and try its different practice case. Use every check in the full task. Watching alone does not mark the lesson complete.
What appears on screen
Open the lesson Try the new case Check and revise The full task and its requirements are in the linked lesson.
Watching explains the example. Complete the lesson practice to check what you can do. Explore all visual lessons →
The method
Keep an original copy and produce a cleaned copy plus a change log. Define a duplicate key from the process: repeated names do not necessarily mean duplicate transactions. Normalize only fields where the transformation is justified, such as surrounding whitespace in an identifier. Keep business distinctions intact.
Use AI to suggest checks, not to silently repair facts. Record rejected rows with reasons and retain them for inspection. Check joins for one-to-many expansion: a joined table with more rows can inflate totals. For scanned inputs, compare OCR values with the original; plausible text is not verified extraction. Without image capability, use typed fixtures and label that limitation.
Choose your model, harness and offline route
Text-only assistant
Use supplied text. Ask for a plan or draft; do not claim browsing, code execution or file access that was unavailable.
Tool-enabled application or harness
Record which tools actually run, allowed files and actions, approval boundaries and observable results. Tools are optional unless the task needs them.
Local or cloud deployment
Inspect inference, retrieval, connectors, logs and storage separately. Use only authorized environments for real data; training fixtures are fictional.
Higher-capability or frontier model
Evaluate the exact task and configuration. Capability does not supply missing evidence or grant permission. No provider ranking or guarantee is implied.
Offline alternative
Use the supplied inputs and inspect the worked example manually. Builder exercises use local deterministic Python. No paid account or live model call is required.
Record the exact product/model label when available, tools actually used, inputs, permitted actions, observed output and checks. Live accounts or APIs may have external costs; they are optional and are not used by this page.
Read the general workflow guide →Worked example
Supplied input
Fictional rows: R1 amount 10; R1 amount 10 repeated; R2 amount 15; R3 amount blank. Rule: one row per request ID; repeated identical IDs are duplicates; missing amount needs review.
A checked result
Cleaned accepted rows: R1=10 and R2=15. Log the duplicate R1 and hold R3 as missing. Known total 25 across two accepted records; full total unknown.
Why this works
The cleaning decision is justified by a supplied key rule, and missing value treatment remains visible.
Try a different case
Independent rows: J1, Item A, quantity 2; J1, Item A, quantity 2 repeated; J2, Item A, quantity 3; J3, Item B, quantity -1. Rule: IDs identify unique issues; quantities must be positive. Produce accepted and review tables.
Download this task and checklist →Interactive practice / Fictional fixture
Clean without losing legitimate records
Use the supplied independent case above. Check the bounded decisions, then review your written artifact against the full acceptance criteria.
Everything runs in this browser. No AI model is called and no work is uploaded. Bounded checks cover specific facts and rules. Your writing is not graded: you review it yourself. Practice never marks a lesson complete automatically.
Preserve the original rows and explain duplicate and invalid-row handling.
Try the task, run the checks, then revise. Checks are teaching feedback, not an instructor grade.
Observed browser simulation
This trace comes from the bounded local checker. It is not a Python run, model result or production tool execution.
Keep or restore your work
Drafts are not saved automatically. Save only fictional practice data. A browser draft may be lost if storage is cleared; download a copy to keep it.
Your steps
- Keep the original four rows.
- Remove only the repeated J1 under the supplied rule.
- Quarantine J3 and compute the accepted quantity.
Check your work
- Accepted quantity is 5 from J1 and J2.
- Item A repetition alone is not a duplicate key.
- Preserve J3 for review and report incomplete coverage.
After your attempt: review notes and a common mistake
Deleting J2 because it shares an item name destroys a legitimate record. Replacing -1 with 1 fabricates a quantity. Keep the audit trail.
Different wording or a different justified decision can pass. Check your work against every requirement; these notes are not an automated grade.
Explain your approach
Which cleaning rule depends on business meaning rather than formatting?
Keep your artifact, one correction or decision, its evidence and one remaining limitation. Nothing on this page sends or uploads your work.
Check your understanding
Choose an answer to see feedback. This is a knowledge check, not instructor assessment.
Review your independent practice
Complete the supplied task before recording progress. Use the acceptance criteria and reveal the review notes after your attempt. Keep your artifact yourself; nothing is uploaded.
Not yet self-reviewed. Completion stays in this browser and does not issue a badge.
Optional deeper study
Explore the related general-course lesson → This opens the existing canonical lesson; it is optional depth, not another copy of this focused task.
Primary references
Revision 2026-10-03. Fictional examples, not observed provider results. Knowledge checks and completion records are self-study aids.