An office-services coordinator receives a canteen questionnaire export. One person answered "No," another left the cell empty, and a third entered an unrecognized code. Before drafting a preference summary, the preparation role needs to keep those situations separate.
The UK Government Data Quality Framework distinguishes completeness from accuracy: a dataset can have every field filled and still contain incorrect values.[1] Replacing a blank with a guessed answer would make this example look more complete without establishing what the respondent meant.
This note proposes a MAJLS survey-preparation Virtual Employee for one internal task: preserve received responses, apply an agreed codebook and prepare question-specific counts for human review. It is an operating design, not a demonstrated integration or client result. Every questionnaire, record and result below is synthetic and illustrative.
Filled example: agree the questionnaire first
Use this fictional codebook, version canteen-example-v1:
| Question | Exact wording | Permitted answers | Eligibility and share denominator |
|---|---|---|---|
| Q1 | Did you use the office canteen in the last five working days? | Y = yes; N = no; NA = no on-site access during this period | All supplied rows except explicit NA; use valid Y/N answers for the yes share |
| Q2 | If Q1=Y, which pickup point would you prefer next week? | C = counter; H = collection shelf | Only Q1=Y; use valid C/H answers from eligible rows for the preference share |
The Q2 instruction allows other respondents to leave it blank or enter NA. Under this codebook, a blank Q1 stays eligible but unanswered. For Q2, a missing or invalid Q1 leaves eligibility unknown. These are explicit example rules, not universal survey conventions.
In this illustration, Mina Vale owns the questionnaire. In a real run, the named human questionnaire owner confirms wording, codes and eligibility before the working map is used. An export without that information stays on hold. A response to Q2 cannot establish a missing Q1 answer.
Keep raw values beside the working map
Retain the received file unchanged. In a separate working layer, this example permits trimming surrounding spaces and uppercasing Latin codes. It permits no replacement of missing answers.
The raw-to-working sheet below contains all ten synthetic rows. blank means an empty received cell; quotes around " y " show its spaces rather than characters in the answer.
| Record | Raw Q1 | Raw Q2 | Q1 working state | Q2 working state |
|---|---|---|---|---|
| S01 | Y | C | Y, observed | C, observed |
| S02 | N | NA | N, observed | Excluded: Q1=N |
| S03 | blank | blank | Missing | Unknown eligibility: Q1 missing |
| S04 | NA | NA | Excluded: explicit NA | Excluded: Q1=NA |
| S05 | X | C | Invalid code | Unknown eligibility: Q1 invalid |
| S06 | Y | blank | Y, observed | Missing, eligible |
| S07 | " y " | H | Y, observed after normalization | H, observed |
| S08 | Y | Z | Y, observed | Invalid code, eligible |
| S09 | N | blank | N, observed | Excluded: Q1=N |
| S10 | Y | C | Y, observed | C, observed |
S02's N is an actual negative answer to canteen use. S03 supplies no answer. S04 supplies an explicit exclusion code. S05 supplies an invalid code, even though its Q2 value looks usable. Combining those rows under "No" would change their meaning.
Keep received Q2 values even on excluded rows. S09's empty follow-up is not missing among eligible Q2 respondents; the skip rule excludes it. If an ineligible row contains C or H, flag the contradiction for the questionnaire owner instead of admitting it to the preference denominator.
Filled denominator sheet: count each question separately
Local code calculated these counts from the ten rows; no Power Query session or real survey was run. The companion calculation record preserves the raw-file hash, working sheet, partition checks and ten boundary tests.
| Measure | Q1: canteen use | Q2: pickup preference |
|---|---|---|
| Confirmed eligible rows | 9 | 5 |
| Valid observed answers | 7 | 3 |
| Explicit negative answers | 2 N | Not a yes/no question |
| Missing among eligible rows | 1 | 1 |
| Invalid among eligible rows | 1 | 1 |
| Excluded rows | 1 explicit NA | 3: Q1=N or NA |
| Unknown eligibility | 0 | 2: Q1 missing or invalid |
| Observed answers / eligible rows | 7/9 = 77.8% | 3/5 = 60.0% |
| Reported answer share | 5 Y / 7 observed = 71.4% | 2 C / 3 observed = 66.7% |
Check the partitions before writing: Q1 has seven observed, one missing, one invalid and one excluded row. Q2 has three observed, one missing, one invalid, three excluded and two unknown-eligibility rows. Each question accounts for every received row.
The answer share and the observed-answer fraction answer different questions. For Q2, 66.7% describes counter choices among three valid answers; 60.0% describes how many of the five confirmed eligible rows supplied a valid answer. Neither includes the two rows whose eligibility is unresolved.
Replace the unsupported summary
Reject: "67% of employees want counter pickup, so we should change the service."
Proposed internal draft: "In this synthetic export, two of three valid pickup-preference answers selected the counter. Five rows were confirmed eligible: one lacked an answer and one had an invalid code. Three rows were excluded, and two had unresolved eligibility."
That wording describes this working sheet. It does not estimate preferences across all employees, assign preferences to nonrespondents or authorize a service change.
If your team already uses Power Query
Microsoft documents that Power Query profiles the first 1,000 rows by default; selecting the lower-left profiling message lets users switch to the entire dataset.[2] Check and record that scope for the export you intend to summarize.
The documentation also describes an "Unknown" column-quality state when errors leave the remaining data's quality unknown.[2] Keep tool errors separate from unanswered cells and questionnaire-invalid codes. A tool-valid value still needs the codebook and eligibility checks above.
Human review checklist
The framework recommends prioritizing quality dimensions around user and business needs.[1] For this task, use an inspectable denominator rather than a generic cleanliness score:
- Mina Vale, the illustrative human questionnaire owner, reviews codes, skipped rows and unresolved eligibility. Current record: S05's X and S08's Z remain unresolved; no substitute is approved.
- Noor Hart, the illustrative human office-services owner, reviews the summary and intended audience. Current record: draft only; circulation and service decisions are not approved.
- Hold on a missing codebook, duplicate record token, undocumented replacement, failed count partition or incomplete profiling scope. Keep the reason and source row visible; model confidence does not resolve these conditions.
Consequential approval remains with the named human service owner, including circulation, supplier selection, ordering and policy changes. The preparation role does not contact respondents or take those actions. Start by filling this coding and denominator sheet for your own approved questionnaire, then ask its owner to review the exceptions before drafting a conclusion.
Government framework material: © Crown copyright 2020, Open Government Licence v3.0, except where otherwise stated.[1]