Power Query try Passed. The Nested Field Still Failed.

A Power Query step returns a record with HasError = false. A later step reads its nested Amount field and raises an error. The tempting diagnosis is that try failed to catch an exception it had already examined. The more useful question is whether that field was examined inside the protected expression at all.

Microsoft's M evaluation model distinguishes returning structured values from evaluating their members. Its error-handling specification includes a deferred record-field failure that escapes an earlier try. The operational task is therefore to identify the consumption boundary: the expression that actually demands the value the report needs.

This playbook uses six synthetic rows to keep a valid amount, legitimate zero, approved null, missing field, source error and wrong type distinct. Two standalone queries compare eight evaluation boundaries and build a flat, row-level audit. The proposed repair protects scalar consumption and the admission decision, rather than treating a returned container as a certificate of valid contents.

Execution disclosure: both supplied queries are UNEXECUTED. No Power Query engine was available during preparation or this review. Every fixture result below is EXPECTED UNTIL EXECUTED, not a locally observed measurement. Host-specific support for BufferMode.Eager is unconfirmed.

The scope is deliberately local. No external data source is needed, and no claim is made about connector reliability, production refresh completeness or performance. Acceptance requires the recorded host's actual scalar results to match the declared policy.

State what the successful try actually evaluated

Start by naming the expression protected by each try. For try error Error.Record(...), evaluation immediately raises an error. For try [Amount = error Error.Record(...)], the protected expression returns a record whose field can remain deferred. Those are different requests, even though both expressions contain the same error-producing operation.

In this fixture, HasError = false on the second expression is an expected statement about obtaining the record, not about successfully reading Amount. The later access must have its own appropriate boundary. Calling the first result a clean amount confuses the result's shape with the value eventually required by the consumer.

Keep four evidence categories separate. Microsoft documentation supplies the language behavior. The preparation record supplies the fact that the examples were not executed. Static review supplies reasoning about the written expressions and their expected policy outcomes. Only a recorded run in a named host can supply observations about these particular queries. None of those categories substitutes for another.

The practical consequence is to write narrow acceptance statements. “The record was returned” is narrower than “the amount was consumed.” “The amount was consumed and classified” is narrower than “the full source was enumerated and every required row retained.” A review should say which statement its evidence supports.

The two fixtures test those statements at different levels. The microprobe exposes where error capture is expected to change. The audit checks the complete six-row result against a literal oracle, meaning an independently specified expected answer. A pass in one fixture does not excuse an unexplained difference in the other.

Do not promote a diagram, source listing or expected table into runtime evidence. Until execution occurs, the operational decision remains HOLD. That limitation does not invalidate the documented mechanism; it prevents a stronger, unsupported claim about a particular installation.

Record the host and create literal test data

Create two separate blank queries in an owned Excel Power Query or Power BI Desktop environment. Open each query's Advanced Editor and replace its contents with the corresponding complete source below. Name the queries lazy_error_probes and lazy_error_audit. Each defines its own literal Source; neither references the other query.

An Excel workbook may serve as the local host and output destination, but no workbook is required as an input data source. Likewise, a local Power BI Desktop file is a container for the exercise, not evidence of a service refresh. Do not attach credentials, a gateway or a production dataset to make this fixture run.

Use the following as an environment-record checklist, not as a completed execution log. Capture values from the installation actually used. Where an engine detail is not exposed, record that limitation instead of copying a version from documentation or another team's machine.

Preserve the initial six rows before making experiments. Keep copies of both complete queries with the run record so that a later reviewer can distinguish the baseline from an adaptation. The supplied revision is 2026-10-17-batch2; it is a fixture identifier, not a software version.

Keep zero, null, missing and failed values separate

R1 contains numeric 10. R2 contains numeric zero, which is legitimate and must remain visible. R3 contains an explicit Fixture.Source error in Amount. R4 contains a present Amount field with null, approved by this fixture's policy. R5 has a Label field but no Amount. R6 has a present Amount containing the text "ten".

The classifier permits present nulls generally; it does not implement a RowID-specific null entitlement. R4 is the only null in this baseline, and the oracle requires that exact classification. A production rule allowing null only for certain records needs an additional, separately tested policy.

This discipline connects to BI data-cleaning and modelling foundations, but the exercise remains narrower: identify when one nested amount is evaluated, and preserve the meaning of every resulting state.

Build the eight-case lazy-evaluation microprobe

Paste the complete lazy_error_probes.m source into the first blank query. The source below is the supplied baseline, not a shortened sketch. Retain the final if expression: it makes the query's successful result depend on the comparison instead of storing a check in an optional field.

lazy_error_probes.m
let
    Source = #table(type table [RowID = text, Payload = record], {
        {"R1", [Amount = 10]},
        {"R2", [Amount = 0]},
        {"R3", [Amount = error Error.Record("Fixture.Source", "Inert amount failure", "R3")]},
        {"R4", [Amount = null]},
        {"R5", [Label = "missing"]},
        {"R6", [Amount = "ten"]}
    }),
    Direct = try error Error.Record("Fixture.Direct", "Inert direct failure", null),
    Container = try [Amount = error Error.Record("Fixture.Nested", "Inert nested failure", null)],
    Late = try Container[Value][Amount],
    TableTry = try Source,
    Buffered = try Table.Buffer(Source, [BufferMode = BufferMode.Eager]),
    BufferedLate = try Buffered[Value]{2}[Payload][Amount],
    RecordWrapped = try [Value = Source{2}[Payload][Amount]],
    WrappedLate = try RecordWrapped[Value][Value],
    Checks = #table(type table [Case = text, ActualHasError = logical, ExpectedHasError = logical], {
        {"M1 direct error", Direct[HasError], true},
        {"M2 record constructed", Container[HasError], false},
        {"M3 nested field consumed", Late[HasError], true},
        {"M4 table returned", TableTry[HasError], false},
        {"M5 shallow buffer returned", Buffered[HasError], false},
        {"M6 buffered nested field consumed", BufferedLate[HasError], true}
        {"M7 extra record wrapper", RecordWrapped[HasError], false},
        {"M8 wrapped field consumed", WrappedLate[HasError], true}
    }),
    Matches = List.AllTrue(List.Transform(Table.ToRecords(Checks), each [ActualHasError] = [ExpectedHasError]))
in
    if Matches then Checks
    else error Error.Record("Fixture.Expectation", "A lazy-evaluation expectation differed", Checks)

ActualHasError names a column calculated by the query when executed. Its name does not mean actual values were obtained during preparation. ExpectedHasError contains the predefined assertions. Compare both columns in the eventual eight-row output and preserve that output with the host record.

The expected sequence, in case order M1 through M8, is true, false, true, false, false, true, false, true. These are error-capture flags, not success flags. M1, for example, is expected to succeed as a test by reporting that its deliberately raised error was caught.

The List.AllTrue documentation defines the check as true when all its input expressions are true. Applied here, the successful branch requires the eight comparisons to agree. Do not reinterpret a failure as a partially successful qualification: preserve the discrepancy and investigate it.

On failure, the query raises Fixture.Expectation, with Checks supplied as diagnostic detail. That is a failure path, not a promise that a host's error pane has already displayed every diagnostic member. Inspect and safely consume the individual checks when investigating; do not assume the exception display is a complete execution transcript.

Run the final query, rather than selecting only its Source or Checks step. An intermediate preview can bypass the terminal assertion. For the baseline qualification, also retain all eight scalar rows from the final output. An unexpected result requires the exact expression, error reason and host details, not a screenshot captioned simply “Power Query failed.”

Compare direct errors with deferred record fields

M1 establishes the direct-error control. Its protected expression is an explicit error expression. There is no intervening record field whose consumption can be postponed. The expected Direct[HasError] is therefore true, and the synthetic reason is Fixture.Direct. This control checks the basic capture path before introducing a container.

M2 changes the request. Container is the result of trying to construct a record with an Amount field defined by an error-producing expression. The expected Container[HasError] is false because obtaining that record does not itself demand the field's scalar value. This is the point at which a misleading “validated” label can enter a pipeline.

M3 then asks the question that M2 did not: can the nested amount actually be obtained? Its expression, try Container[Value][Amount], first selects the successful result's record and then selects Amount. The second selection reaches the delayed failure within the new protected expression. The expected result changes to Late[HasError] = true.

The distinction is between operations, not two interpretations of the same completed calculation. M2 and M3 can both behave as expected without an engine bug, an unreliable exception handler or a transient source problem. The synthetic fixture requires none of those explanations.

Locate the operation that actually forces the error

Mark Container[Value][Amount] as the consuming expression in M3. Merely assigning a name to a deferred expression is not sufficient. In particular, a let binding can remain lazy; its later use determines when the value is demanded. The repair must preserve protection at that later demand, rather than merely move text into a differently named step.

Microsoft's field-access rules distinguish selecting a field from evaluating unrelated fields. This is why reading one safe property of a record is not evidence about every other property. Review the exact access chain, including each record returned along the way.

A function boundary does not change the acceptance question. A function that returns a record can expose another deferred field to its caller. Naming the function ValidateAmount would not strengthen the result. Its returned representation and the actual consumer still determine what has been examined.

Keep this separate from successful parsing with the wrong date culture. That comparison concerns a computed value whose business interpretation is wrong. Here the first outer try may not yet have demanded the dangerous value. Reusing a date example would obscure the evaluation boundary rather than explain it.

For review, state both the expected flag and the protected expression. “M2 false; M3 true” without the access paths is easy to misread as instability. With the paths preserved, it describes the intended negative control and the later capture of the deferred error.

Test the difference between a returned table and its cells

M4 asks whether try Source can return the literal table. The expected answer is HasError = false. The source has six trusted row identities and a Payload column containing records. R3's problematic amount lives inside one of those records; obtaining a table-valued result is not a recursive certification of that amount.

The source schema is intentionally RowID = text, Payload = record. It identifies the outer column kinds. It does not declare that each payload contains an evaluated numeric amount. R5's different field set and R6's text amount remain important cases precisely because the outer record shape is not the admission policy.

Distinguish three questions when reviewing an intermediate table: was the table expression obtained, were the intended rows produced, and were their required scalar members successfully classified? A table preview that shows record cells may help answer the first question while leaving the other two unresolved. Do not rename that step “All amounts validated.”

In the second fixture, Table.ToRecords supplies row records to Classify. That conversion is part of the traversal, not the final acceptance assertion. The consuming classifier and terminal comparison remain necessary parts of the proposed design.

The trusted-container assumption matters here. The fixture starts with a known literal table whose RowID and Payload containers are available. It injects an error into one nested scalar expression. It does not inject a failure into the operation that obtains the next row from an arbitrary source.

For an external source, an enumeration failure could prevent a row from becoming available to the classifier. A per-row try cannot be credited with preserving an identity it never received. Similarly, this test does not qualify a payload container that fails before its fields can be inspected. Such cases need separate sources, expectations and completeness checks.

When adapting the pattern, keep the distinction explicit in the run record: a classified rejection is an accounted-for row, whereas an interrupted enumeration may leave the population unknown. Both can produce errors, but they require different operational decisions. The first may support quarantine; the second ordinarily requires holding acceptance until completeness is established.

The declared six-row source also makes completeness independently knowable. In a real input without an authoritative expected population, counting the rows that happened to reach Classify would not prove that other rows were never lost. Keep that evidence gap visible rather than borrowing the literal fixture's certainty for a different source. A successful local result qualifies this controlled scenario only.

Do not add an upstream connector to this baseline to investigate that question. Preserve the literal exercise as the control, then qualify the different boundary in a separately named test.

Show why Table.Buffer is not a deep error scan

M5 and M6 test a common proposed shortcut: buffer the table, then treat it as fully checked. The expected M5 result is false, meaning the buffering expression itself does not capture an error in this setup. The expected M6 result is true when the buffered table's R3 payload amount is subsequently demanded.

Microsoft's Table.Buffer documentation describes a shallow operation. Scalar cell values are evaluated, while nested records, lists and tables are left as nonscalar values. In this fixture, the Payload cell is a record. The error-producing Amount expression is one level below that cell, not a scalar column being independently audited.

Read Buffered[Value]{2}[Payload][Amount] from left to right. Buffered[Value] selects the successful buffered result. The zero-based position {2} identifies the third baseline row, R3. Selecting Payload obtains that row's record; selecting Amount reaches its deferred expression. Keep the source order unchanged when using this positional microprobe.

Separate buffering from recursive validation

The BufferMode.Type reference distinguishes immediate buffering from buffering delayed until the data is needed. Its description of BufferMode.Eager concerns timing. Read it together with the function's shallow-buffering contract, not as a separate promise to recursively inspect nested fields.

The distinction is operationally useful: “when does buffering occur?” and “which values does buffering demand?” are different questions. Choosing eager timing does not redefine the nested payload as a flat scalar column. The microprobe leaves the shape unchanged so that the later field access remains visible.

Host compatibility is a separate qualification. Documentation listing BufferMode.Eager does not identify the version installed on the reader's machine. Record whether the original syntax executes there. If it is unsupported, keep the original fixture on HOLD; a separately named variant may be useful, but its results must not be reported as an unchanged baseline pass.

Do not recommend adding a buffer as a universal performance repair. Microsoft warns that buffering can add memory and reading costs and may make a query slower. Those costs are another reason not to insert it when the requirement is simply to validate an amount. No performance measurement is supplied here.

Interpret the pair together rather than treating M6's true flag as sufficient. If buffering itself failed, a later attempt to select Buffered[Value] could raise a different error because that successful-result field is unavailable. M6 could then still report true for the wrong reason. M5's expected false flag is therefore an essential companion control. The baseline microprobe compares flags, not every reason string; inspect error provenance when a discrepancy needs diagnosis. Do not infer “the nested source failure was caught” solely from one true Boolean.

The useful result is narrower than “buffering is bad.” M5 and M6 isolate a boundary that buffering does not cross in this record-column fixture. Use buffering only for an independently justified purpose; use the scalar classifier and full audit for this acceptance contract.

Reject a second record wrapper as the repair

M7 deliberately wraps the dangerous access in another record: try [Value = Source{2}[Payload][Amount]]. This resembles a repair because the source access appears textually inside try. However, the protected expression returns a new record, and its Value field can still defer the access that matters.

The expected M7 flag is false. M8 then consumes RecordWrapped[Value][Value]: the first Value selects the successful try result, and the second selects the wrapper's field. That later consumption is expected to report HasError = true. The extra record did not establish that the amount had been safely evaluated earlier.

Treat this pair as a negative control, meaning a deliberately inadequate repair that the test must distinguish from the real repair. Without it, an author could move the expression under another container, see a successful outer result and repeat the original mistake with a more reassuring name.

This is not an argument against records. They are useful for representing related properties together. The relevant comparison is customer-boundary checks for fill-down donors: bundling a value with provenance serves a different purpose from proving that every member has already evaluated. Do not import that workflow or an ordering claim into this error-boundary test.

The corrected classifier also returns a record, so its safety cannot rest on the word “flat” alone. Its fields refer to a protected scalar attempt and controlled diagnostics, and the terminal audit subsequently demands all expected scalar results. Both parts matter. Returning its record without consuming the audit would weaken the evidence again.

Review wrapper proposals with one concrete question: what operation must complete before this query can return the result being accepted? If the answer is only “construct another record,” the proposed repair has not demonstrated the consumption required by this fixture.

Keep M7 and M8 after implementing the classifier. They document why the superficially similar wrapper was rejected and help a later maintainer understand which refactoring would undermine the design. The test is not satisfied by making every HasError flag false; several true flags are necessary evidence that the deliberate failures remain detectable.

Catch the scalar access and classify its type

Paste the complete lazy_error_audit.m source into the second blank query. It retains all six identities and returns only the columns used by the policy and reconciliation. Its listed outcomes remain expected until the exact query is executed.

lazy_error_audit.m

let

    Source = #table(type table [RowID = text, Payload = record], {

        {"R1", [Amount = 10]},

        {"R2", [Amount = 0]},

        {"R3", [Amount = error Error.Record("Fixture.Source", "Inert amount failure", "R3")]},

        {"R4", [Amount = null]},

        {"R5", [Label = "missing"]},

        {"R6", [Amount = "ten"]}

    }),

    Classify = (row as record) as record =>

        let

            Attempt = try (

                if not Record.HasFields(row[Payload], "Amount") then

                    error Error.Record("Fixture.Missing", "Amount field is required", row[RowID])

                else

                    let Value = row[Payload][Amount]

                    in

                        if Value = null then null

                        else if Value.Is(Value, type number) then Value

                        else error Error.Record("Fixture.Type", "Expected a number or approved null", row[RowID])

            ),

            HasError = Attempt[HasError],

            Reason = if HasError then Attempt[Error][Reason] else null,

            Status = if HasError then

                (if Reason = "Fixture.Source" then "SOURCE_ERROR"

                 else if Reason = "Fixture.Missing" then "MISSING_FIELD"

                 else if Reason = "Fixture.Type" then "TYPE_REJECT"

                 else "UNEXPECTED_ERROR")

                else if Attempt[Value] = null then "NULL_APPROVED"

                else "VALID"

        in

            [RowID = row[RowID], Status = Status,

             Amount = if HasError then null else Attempt[Value], ErrorReason = Reason],

    Audit = Table.FromRecords(

        List.Transform(Table.ToRecords(Source), each Classify(_)),

        type table [RowID = text, Status = text, Amount = nullable number, ErrorReason = nullable text]

    ),

    Ordered = Table.Sort(Audit, {{"RowID", Order.Ascending}}),

    ActualRows = Table.ToRows(Ordered),

    ExpectedRows = {

        {"R1", "VALID", 10, null},

        {"R2", "VALID", 0, null},

        {"R3", "SOURCE_ERROR", null, "Fixture.Source"},

        {"R4", "NULL_APPROVED", null, null},

        {"R5", "MISSING_FIELD", null, "Fixture.Missing"},

        {"R6", "TYPE_REJECT", null, "Fixture.Type"}

    }

in

    if ActualRows = ExpectedRows then Ordered

    else error Error.Record("Fixture.Reconciliation", "Row-level audit differed", ActualRows)

The Record.HasFields contract supports a presence test before interpreting the field's value. In this fixture, that creates a deliberate Fixture.Missing classification for R5 instead of confusing absence with R4's present null.

The important boundary is the entire Attempt = try (...) expression. let Value = row[Payload][Amount] introduces the binding, but does not by itself prove eager consumption. The following null comparison demands Value inside the same protected expression; the number decision then determines whether a non-null value is admissible.

The Value.Is reference defines compatibility checking, not conversion. R6's text therefore receives the fixture's type rejection instead of being interpreted as numeric 10. The policy accepts the specified number type or an approved present null. It does not establish additional business rules for precision, range or every possible numeric representation.

Return safe flat diagnostics instead of the original payload

The output contains RowID, Status, Amount and ErrorReason. On a rejected row, null in the output amount means that no amount was admitted; it is not a reclassification of the source as an approved null. The status and reason retain that distinction.

Read HasError before choosing between Attempt[Value] and Attempt[Error]. Microsoft's conditional error-handling guide describes these alternative result paths. A null displayed after expanding a missing result field is not evidence that both fields are available on the original result record.

For these synthetic errors, Error.Record provides controlled reasons and inert details. The classifier retains the reason, not arbitrary nested diagnostic content. Its direct reason access is justified by this fixture's controlled errors, not a general sanitization promise for every error object.

Do not generalize the word “safe” beyond those columns and assumptions. Here, RowID comes from trusted literals, status is selected from fixed labels, and error reasons come from controlled fixture errors. An adaptation that copies arbitrary messages or nested error details must validate that diagnostic path separately. Error reporting can itself demand values; placing it outside the scalar attempt does not automatically make it harmless. Preserve only the evidence required for the authorized operational decision.

The original Payload is intentionally absent. Carrying it alongside a “clean” amount would allow a downstream consumer to select R3's dangerous field again. Keep an authorized original separately when necessary; do not expose it as though the flat audit had recursively repaired it.

Force the full row audit before declaring acceptance

Audit is built from the classifier's records, with explicit scalar column types supplied to Table.FromRecords. It is sorted by RowID for the fixture comparison. ExpectedRows independently declares all six identities, statuses, amounts and reasons; it is not generated by running the same classifier twice.

Table.ToRows produces nested lists of row values. The assertion is not the conversion alone. It is the outer if ActualRows = ExpectedRows then Ordered else error ..., which makes the successful returned table depend on the comparison.

Under Microsoft's list-equality rules, equal lists have equal lengths and equal corresponding items in order. Applied to this finite scalar audit, a successful comparison requires the prescribed six rows and their values to agree. Missing identities, extra rows, changed statuses and lost zeros cannot be justified by an unchanged total.

The conditional-expression specification requires the condition to determine which branch is evaluated. Consequently, evaluating this final expression successfully is materially different from merely obtaining an intermediate table. Do not claim that Power Query previews inherently bypass if: a genuine evaluation of this final query must determine its condition too.

The operational qualification nevertheless requires the complete final output, not an ambiguous preview screenshot. Load or otherwise fully consume both final scalar tables in the recorded host, retain the eight microprobe rows and six audit rows, and confirm which query revision produced them. Do not qualify an earlier selected step or stale captured output.

A comparison may establish a mismatch before every later diagnostic value has been inspected. Therefore, a failing gate blocks acceptance but does not automatically provide an exhaustive inventory of all faults. Preserve the error and inspect the safe audit for diagnosis. Never relabel an incomplete failure investigation as a complete row audit.

Avoid replacing the terminal assertion with a record containing Data and an unused Gate. A consumer that selects only the data could recreate the same deferred-check problem. The successful public result should depend on the validation condition, not merely travel beside an expression capable of validating it.

A useful separately recorded negative control is to change only one expected classification in an owned copy while leaving the source unchanged. For example, expect R2 to be NULL_APPROVED rather than VALID. The required outcome is a reconciliation failure, even though the underlying numeric total remains 10. This control is proposed and unexecuted, not an additional reported result. Restore the baseline oracle afterwards. Its purpose is to check that the acceptance decision actually depends on the asserted row policy, rather than on an unused comparison or a selected intermediate step.

For this baseline, retain the output exactly as emitted, including rejected rows. Filtering quarantine rows before reconciliation would change the population being tested and conceal the evidence needed to decide whether the classifier behaved correctly.

Choose ACCEPT, QUARANTINE, REPAIR, or HOLD

These decisions operate at different levels. ACCEPT describes qualification of the fixture and, separately, admission of eligible row values. QUARANTINE retains a rejected row's safe identity and diagnostics. REPAIR_AND_RERUN authorizes a controlled correction followed by renewed evidence. HOLD means the evidence needed for a decision is missing or contradicted.

A correct fixture run can be accepted while three input rows remain quarantined. That is not contradictory: rejecting the intended bad rows is part of the successful test. Conversely, a query that produces only apparently clean amounts may fail qualification because it lost rejected identities or erased their meanings.

Treat the host requirement and the policy requirement separately. Successful parsing of BufferMode.Eager is not sufficient if the audit is wrong. A matching audit is not sufficient to claim the original microprobe passed on an unsupported host. Likewise, a present null requires an approved business rule; successful evaluation alone does not grant that approval.

Keep error replacement out of the acceptance policy

try row[Payload][Amount] otherwise 0 would make a source failure resemble R2's legitimate zero when the amount is viewed alone. Using otherwise null would make the same failure resemble R4's approved null. Neither expression establishes what the original value meant.

Microsoft documents otherwise as a way to provide an alternative value. This exercise deliberately chooses a different contract: preserve the failure classification instead of substituting a reportable amount. Replacement can be legitimate elsewhere when authorized, but it must not be presented as evidence that the input was already valid.

The null stored for R3 in the audit is safe only with its SOURCE_ERROR status and Fixture.Source reason. Do not publish the amount column alone and claim that all its nulls were approved. The reporting owner must define how accepted values and quarantine records are separated downstream.

An UNEXPECTED_ERROR is not quietly accepted because a diagnostic row exists. It differs from this fixture's oracle and requires investigation. The current unexecuted baseline remains on HOLD even though its expected decisions are fully specified.

Reconcile records without hiding failures in totals

The following is the expected six-row audit, not an observed output. Compare the eventual final table against it without changing the oracle to accommodate unexplained results. Null denotes an M null value here, not the text string "null" or a blank copied from a screenshot.

The expected population is six retained audit records: two accepted numeric values, one approved null and three distinct rejections. The accepted numeric sum is 10 because 10 plus zero is 10. That arithmetic is a consistency check derived from the literal inputs, not runtime evidence.

Several wrong results could preserve the same total. Dropping R2 leaves the sum unchanged. Duplicating zero also leaves it unchanged. Replacing R3's failure with zero can leave the numeric sum unchanged while corrupting the number of accepted records. Losing R4 would remove the approved-null case without changing the sum.

Even counts alone are insufficient. Three accepted and three rejected rows could still contain the wrong identities or reasons. The oracle therefore specifies each RowID, the exact classification and the associated amount and reason. It tests meaning and membership together rather than attempting to infer them from aggregates.

After execution, retain an observed table separately from this expected table. Record discrepancies explicitly, including a missing row or unexpected status. Do not prepopulate an “observed” column from the oracle and later forget which values were copied. Before a run, there is no observed table to present.

For comparison, separate historical-refresh reconciliation addresses whether historical corrections reach a model and report. This exercise does not qualify that coverage. Its entire source is a local literal table; the shared discipline is to reconcile identities and values rather than treating a broad success indicator as sufficient evidence.

Keep the rejection reasons even when a report only needs the accepted numeric sum. The aggregate answers a reporting question. The audit answers whether the underlying rows were admitted or withheld for the right reasons. They are complementary outputs, not interchangeable proofs.

Recover and rerun without dropping diagnostic evidence

When an observed result differs, preserve the original source, query revision and available safe diagnostics before editing. Identify whether the difference concerns host support, an incorrect evaluation boundary, a source value or the acceptance policy. Changing all four at once makes the next result difficult to interpret.

Use a controlled recovery sequence:

1.      Retain the baseline RowIDs, complete queries and prior scalar audit evidence.

2.      Correct the identified issue in an owned copy, documenting the intended classification change before running it.

3.      Rerun both unchanged baseline queries in the recorded host, then run the separately identified repaired scenario.

4.      Compare actual outputs with the appropriate predeclared oracle and retain differences before deciding on promotion.

A source correction requires its own expected outcome. For example, changing R3 from a deliberate error to an authorized amount should change its status. The original ExpectedRows must then fail if used unchanged. Do not interpret that failure as evidence against the classifier; distinguish the original negative control from the repaired-input scenario.

Keep the original baseline intact so that the deliberately bad input remains available for regression testing. An approved policy change similarly creates a new test revision. Never edit the oracle after seeing an unexplained result solely to make the query return a table.

The literal fixture creates no external source records, so there is no source-side cleanup command. After preserving the evidence, remove only the local scratch queries or output artifacts that are no longer needed. In later adaptations, retain originals and diagnostics under the applicable access policy rather than exposing user data in public examples.

Do not refresh production datasets blindly or delete error rows to produce a reassuring preview. Recovery means correcting the cause and regenerating auditable evidence, not merely hiding the symptom that made the defect visible.

Assign owners for evaluation and data-quality contracts

The M author owns the evaluation boundary. That person should identify the protected expression, the demand for Amount, the guard selecting Value or Error, and the terminal assertion. Code review should also confirm that the original payload has not been reintroduced into the accepted output.

The source owner owns the meaning of zero, null, absence and a failed source expression. A missing field must not become an approved null through an undocumented convenience change. Where null eligibility depends on business context, the source owner must provide the rule and the examples needed to test it.

The reporting owner decides what quarantine means for the consuming report. The technical fixture can retain rejected records correctly without authorizing their publication as valid data. Document whether rejected inputs block the report, appear in an exception view or require a separately approved handling route.

These responsibilities fit a broader BI governance and reporting context, but general governance discussion does not establish this query's behavior. The evidence remains the exact code, declared policy, documented language rules and recorded host outputs.

Require reruns after a material host, build, option, query or policy change. A run record should contain the environment, source revision, expected assertions, actual outputs, compatibility result, discrepancies and decision owner. A past pass belongs to the conditions under which it was obtained.

Report the HasError documentation discrepancy and test limits

The M error-handling specification contains an internal inconsistency: its schematic result records use HasErrors, while concrete examples use HasError. The product error-handling guide also uses the singular form. Both supplied queries use HasError; no documented API rename or version transition is inferred from the discrepancy.

Keep the test's trusted-container limitation alongside the evidence. Classify receives a row record; it does not prove complete enumeration of an arbitrary failing source. Its controlled error reasons do not validate every possible diagnostic object. Microsoft discusses step-level and nested-value errors separately, which should not be collapsed into this one scalar fixture.

Finally, retain the execution status honestly. These sources have been reviewed against documentation, but neither query has been run here. An unexecuted expected table is a reproducibility aid, not a certified result. Every extension beyond the literal containers, controlled reasons and stated number/null policy needs its own evidence.

Build stronger BI validation practice

The transferable practice is to make acceptance depend on the value the consumer actually needs. A returned record is not a checked amount, another wrapper is not a repair, and a shallow buffer is not a recursive audit. For this fixture, qualification requires the recorded eight probe results and the complete six-row scalar reconciliation.

Readers developing the surrounding foundations can review Business Intelligence Essentials. Refonte Learning describes three months at 8–10 hours per week, with data analysis, SQL, visualization, practical projects and personalized mentorship. Its FAQ names Power BI, Excel, Tableau and SQL-based platforms. The page recommends basic spreadsheet familiarity, separately requires working toward a bachelor's or higher-level degree, and describes a Training Certificate and Certificate of Internship upon successful completion.

Those are the provider's programme descriptions, not independent outcome guarantees. The page does not establish that this exact Power Query M laboratory is taught. Keep curriculum exploration separate from technical qualification: the expected results become execution evidence only when the actual queries are run and reconciled.