Errors, Validation and Testing

Introduction

A Power Query transformation is not complete merely because it refreshes without displaying a red error message. A reliable data-integration pipeline must also produce the expected rows, columns, data types, totals, and business meaning. Errors, validation, and testing therefore form a single professional discipline: errors identify defects, validation checks whether outputs are credible, and testing provides repeatable evidence that the process continues to work after data or logic changes.

In Power BI, Power Query performs extraction, transformation, and loading (ETL). Its steps are expressed in the M language and are evaluated in sequence. A source may change structure, a date may be invalid, a numeric field may contain text, or a join may duplicate records. Some problems stop refresh immediately; others produce technically valid but incorrect results. The second category is especially dangerous because it can remain invisible until a management decision has already been made.

This chapter develops practical methods for handling errors with try and otherwise, diagnosing failed steps, validating row counts and reconciliations, documenting assumptions, and implementing repeatable testing and audit controls.

Learning Objectives

By the end of this chapter, you should be able to:

• distinguish expression errors, data-quality errors, and logic errors;
• use try, otherwise, and error records appropriately;
• diagnose failed Power Query steps systematically;
• design row-count, completeness, uniqueness, and reconciliation checks;
• document assumptions, exceptions, and control ownership;
• build repeatable tests for refresh and schema changes; and
• evaluate the governance and professional implications of unreliable transformations.

1. Understanding Errors in Power Query

An error is a condition in which Power Query cannot evaluate an expression or a transformation produces an unacceptable result. Errors can be classified by their origin and by whether they are visible.

A useful distinction is between hard failure and silent failure. A hard failure prevents refresh and usually points to the immediate defective step. A silent failure produces an apparently valid table but violates a business expectation. For example, filtering out null transaction IDs may remove 2% of sales records without causing a technical error.

Errors, nulls, and missing values

An error is not the same as a null. A null represents the absence of a value; an error represents a failure to evaluate a value. Converting an error to null may allow a query to continue, but it changes the information available to downstream users. The original error should therefore be retained in an exception column or an audit table whenever investigation may be required.

Evaluation and step dependencies

Power Query evaluates a let expression through named steps. A later step depends on the value returned by an earlier step. If a step refers to a column that no longer exists, the failure may appear at the reference step even though the underlying cause was a source-schema change. Diagnosis must therefore consider both the failed expression and the data entering it.

2. The try and otherwise Pattern

The M expression try expression evaluates an expression and returns a record describing whether evaluation succeeded. Its conceptual result contains fields such as HasError, Value, and Error. A concise alternative is:

try [Amount] otherwise null

This returns the amount when conversion or evaluation succeeds and null when it fails. A more explicit form preserves an operational status:

try Number.From([Amount]) otherwise null

For professional pipelines, replacing every error with null is rarely sufficient. A better design captures both the usable value and the reason for failure. For example:

let
    Attempt = try Number.From([Amount]),
    CleanAmount = if Attempt[HasError] then null else Attempt[Value],
    AmountStatus = if Attempt[HasError] then "Invalid amount" else "Valid"
in
    [Amount = CleanAmount, AmountStatus = AmountStatus]

The exact implementation may instead use separate custom columns. The principle is consistent: preserve the business field for analysis and preserve an exception indicator for control purposes.

When otherwise is appropriate

otherwise is useful when a fallback is explicitly meaningful. Examples include returning a default currency, assigning an “Unknown” category, or allowing a row-level conversion to continue while recording an exception. It is not appropriate when the fallback conceals a critical failure, such as a missing source table or a failed authentication attempt. A missing file should normally stop the refresh or trigger an operational alert rather than become an empty table.

Row-level and step-level error handling

Row-level handling is appropriate when isolated records may legitimately be defective and the remainder of the batch is usable. Step-level handling is more suitable for expected alternative source structures, such as optional columns, but it must be carefully bounded. Broad error suppression can transform a major outage into an apparently successful refresh.

A controlled approach is:

1. identify the error that is expected;
2. define the permitted fallback value;
3. record the exception count and sample records;
4. establish a threshold for escalation; and
5. test both the successful and failing paths.

3. Diagnosing a Failed Step

Diagnosis should be systematic rather than based on repeatedly editing expressions until the error disappears.

A practical diagnostic sequence

First, identify the exact failing step. Select the step in Applied Steps and inspect the error message. Do not assume that the last visible step is the original cause.

Second, inspect the input to that step. Select the preceding step and verify its table type, column names, row count, and representative values.

Third, inspect data types. A column that visually contains numbers may actually be text, include currency symbols, or use a locale-specific decimal separator. Confirm the intended culture and type conversion.

Fourth, isolate a minimal expression. Test the suspected operation on a small sample or a custom column. This distinguishes a defective row from a defective transformation.

Fifth, check structural assumptions. Confirm that expected files, columns, keys, and nested objects exist. A navigation step may fail because an Excel sheet was renamed or a JSON response changed shape.

Sixth, compare with the source. Determine whether the source itself contains the defect or whether an earlier step introduced it.

Seventh, record the diagnosis. A professional pipeline should have an issue log containing date, query, step, error text, affected records, root cause, corrective action, and reviewer.

Common diagnostic messages

Messages such as “The column was not found” usually indicate schema drift or an incorrect prior transformation. “We cannot convert the value” usually indicates type, format, or locale problems. “Expression.Error” is a category rather than a complete diagnosis; the detailed message and the input value must be examined.

Error inspection tools

The Power Query interface allows errors to be kept, removed, or replaced. “Keep Errors” is valuable during development because it creates a focused table for investigation. During production, error rows should generally be separated into an exception output rather than simply deleted. Profiling tools such as column quality, column distribution, and column profile provide useful evidence, but they are samples or summaries and do not replace formal controls.

4. Validation: Proving That a Result Is Credible

Validation asks whether the result satisfies defined expectations. It should occur at several levels.

Schema validation

Schema validation checks whether required columns exist and whether their names, types, and structures are correct. A practical schema contract may specify:

Schema checks should occur near the beginning of the query, before transformations assume that the contract is satisfied.

Row-count validation

Row counts provide a basic completeness signal. Let N_source be the number of source rows and N_output the number of rows loaded after intentional filtering. The difference is:

The difference is meaningful only when expected exclusions are known. If N_expected_exclusions is documented, an unexplained difference can be calculated as:

Here, all quantities are row counts. The equation assumes that exclusions are counted once and that the source and output refer to comparable stages. A non-zero unexplained difference requires investigation; it is not automatically proof of an error.

A percentage comparison can be useful:

This is applicable only when N_source is not zero. It should not be treated as a quality score without a defined tolerance and an explanation of intentional filters.

Completeness and uniqueness

Completeness checks whether required fields are populated. For a required field, the null rate is:

The denominator must be clearly defined, especially when rows are filtered. Uniqueness checks whether a key occurs once where one occurrence is expected. Duplicate keys may arise from source duplication, an incorrect join, or a legitimate one-to-many relationship. The correct response depends on the data model rather than on an assumption that all duplicates are errors.

Reconciliation controls

Reconciliation compares independently calculated totals. For example, a transaction table may be reconciled to a source control total. Let S be the source amount and P the processed amount:

A relative difference may be calculated as:

The relative measure assumes S ≠ 0; when S = 0, use the absolute difference or a separately defined rule. Currency, sign conventions, rounding, returns, and timing must be consistent before comparison. A reconciliation that ignores these assumptions can create false alarms or, worse, false confidence.

Reconciliation should be performed at useful grains, such as date, branch, product category, or currency. A total may agree while one region is missing and another is overstated. Detailed reconciliation is therefore stronger than a single grand total.

5. Designing Repeatable Tests

A test is repeatable when another person can execute it using the same inputs, procedure, expected result, and acceptance criterion. Tests should be designed before defects occur, not only after a failure.

Test layers

Test cases

A test case should contain an identifier, purpose, input condition, procedure, expected result, actual result, status, and evidence location. Boundary cases are particularly important: empty files, duplicate keys, invalid dates, negative amounts, unexpected columns, and a source with zero rows.

Development, test, and production data

Testing against a stable fixture dataset is useful because expected results can be known in advance. Production data should be tested as well, but changing production data makes it difficult to determine whether a result changed because of logic or because of new records. A controlled sample dataset should include normal, exceptional, and boundary records.

Regression testing

Any change to a query, data source, data type, join, parameter, or privacy setting can affect downstream results. Record baseline row counts, totals, error counts, and selected sample outputs. After a change, compare the new values with the baseline and investigate material differences. A baseline is evidence, not an instruction to preserve incorrect historical behaviour; known corrections must be documented.

6. Auditability and Documentation

Controls are effective only when they are understandable and owned. Documentation should record:

• the purpose and scope of each query;
• source systems, files, owners, and refresh frequency;
• assumptions about dates, currencies, time zones, signs, and duplicates;
• business rules and intentional exclusions;
• expected schema and key definitions;
• error-handling decisions and escalation thresholds;
• validation results and reconciliation evidence;
• change history, author, reviewer, and approval date; and
• known limitations and unresolved exceptions.

A useful control record may include the refresh timestamp, source identifier, source row count, output row count, error count, reconciliation difference, test status, and reviewer. These values can be stored in a control table or operational log. The objective is traceability: a reviewer should be able to understand what was processed, what failed, what was excluded, and who accepted the result.

Documentation must distinguish assumptions from facts. “All amounts are in local currency” is an assumption unless supported by a source specification. Undocumented assumptions create hidden dependencies and make future maintenance risky.

7. Applied Case: Monthly Retail Sales Pipeline

The following is an illustrative case.

Background

A retailer combines monthly CSV files from 35 stores into Power BI. Each file contains transaction ID, date, store, quantity, unit price, discount, and net sales. Finance also provides a monthly control total.

Problem

The February dashboard shows a 9% fall in sales, but store managers report normal activity. The refresh succeeds, so the initial suspicion is a business trend rather than a technical problem.

Analysis

The development team records the following observations:

• January contained 350,000 source rows and February contained 342,000;
• the expected February reduction based on store calendars was approximately 1%;
• four files used a comma decimal separator while the query assumed a period;
• 6,800 net-sales values became errors during conversion;
• an error-replacement step changed those values to null;
• a later filter removed rows with null net sales; and
• the grand total reconciliation was not configured to fail when the difference exceeded tolerance.

The technical refresh succeeded because row-level errors were suppressed. The business result was nevertheless incomplete.

Approach and solution

The team implements the following controls:

1. A file inventory verifies that all 35 expected files are present.
2. A schema check confirms required columns and records unexpected columns.
3. Locale-aware conversion is applied using the documented source culture.
4. A conversion-status column records invalid amounts rather than deleting rows.
5. An exception table stores transaction ID, source file, original value, and error message.
6. Source and processed row counts are compared after intentional exclusions.
7. Source and processed sales totals are reconciled by store and month.
8. A threshold requires investigation when the unexplained row difference is non-zero or the reconciliation difference exceeds the approved tolerance.
9. A test fixture containing invalid decimal formats is added to regression testing.

Outcome

The February sales decline is corrected. The 6,800 rows are processed using the appropriate culture, and the remaining exceptions are separately reviewed. The dashboard now displays a refresh status and exception count, while Finance receives a reconciliation report before publication.

Learning points

The case demonstrates that a successful refresh is not proof of a correct result. Error handling must preserve evidence, validation must compare output with independent expectations, and controls must be designed at the same grain as the risk. It also demonstrates why assumptions about locale and exclusion rules belong in formal documentation.

8. Limitations, Governance, and Professional Practice

No validation system detects every defect. A reconciliation can pass if the same error affects both comparison values. Row counts can remain stable while values are shifted between customers. Profiling can miss rare anomalies. Automated tests therefore require sensible control design and periodic human review.

Access controls should protect source credentials, exception data, and audit logs. Error records may contain customer names, addresses, or financial information, so privacy principles require collection limitation, appropriate access, retention controls, and secure sharing. A query should not expose sensitive raw values merely to make debugging convenient.

Changes to transformation logic should follow a controlled process. At minimum, significant changes need an identified owner, peer review, test evidence, and a rollback or recovery plan. Automated monitoring can report refresh failures, row-count changes, error rates, and reconciliation breaches. AI-assisted code generation may help draft M expressions, but generated code must be reviewed for privacy, type, locale, performance, and business-rule correctness. Responsibility remains with the data professional and process owner.

Best-Practice Checklist

Before publishing a dataset or report, confirm that:

• source availability and expected files have been checked;
• required columns and data types meet the schema contract;
• errors are handled deliberately rather than hidden;
• exception rows remain traceable;
• row-count and completeness rules have been executed;
• key uniqueness and join cardinality have been examined;
• totals have been reconciled at an appropriate grain;
• assumptions and intentional exclusions are documented;
• regression tests cover normal and boundary cases; and
• evidence, ownership, approval, and escalation procedures are recorded.

Chapter Summary

Reliable Power Query solutions require more than syntactically valid M code. Errors may arise from sources, schemas, types, structures, or business logic, and silent logic or completeness errors can be more dangerous than visible failures. try and otherwise support controlled row-level recovery, but fallbacks should not conceal critical failures. Effective diagnosis examines the failed step, its input, data types, structure, and source assumptions.

Validation should include schema contracts, row counts, completeness, uniqueness, and reconciliations. Row-count and financial differences are meaningful only when their grain, exclusions, units, signs, rounding, and tolerances are defined. Repeatable testing uses controlled fixtures, boundary cases, regression baselines, and documented acceptance criteria. Auditability depends on preserving exception evidence, recording assumptions, assigning ownership, and controlling changes. Together, these practices create transformation pipelines that are trustworthy, explainable, and maintainable.

Key Terms

Error: A condition in which an expression cannot be evaluated or a result violates a defined rule.

Hard failure: An error that stops a query or refresh.

Silent failure: An incorrect result that does not stop refresh.

Null: An absent or unknown value, distinct from an evaluation error.

try: An M construct that captures the outcome of evaluating an expression.

otherwise: A fallback expression used when a preceding expression produces an error.

Exception table: A table containing rejected or questionable records and diagnostic information.

Schema contract: A documented specification of expected fields, types, and structural rules.

Schema drift: An unintended change in source structure.

Row-count validation: Comparison of expected and observed numbers of rows.

Completeness: The extent to which required values or records are present.

Uniqueness: The condition in which a key occurs no more often than the model permits.

Reconciliation: Comparison of independently derived values to identify differences.

Tolerance: An approved limit within which a difference is accepted.

Unit test: A test of one transformation or narrowly defined function.

Regression test: A test that checks whether a change has damaged established behaviour.

Control total: An independently supplied total used for comparison.

Data lineage: The traceable movement and transformation of data from source to output.

Schema drift: A change in source fields, types, or structure that affects assumptions.

Audit trail: Evidence showing processing activity, decisions, exceptions, and approvals.

Review Questions

5 Short Answer Questions

1. What is the difference between a hard failure and a silent failure?
2. Why is replacing every error with null potentially dangerous?
3. What information should an exception table preserve?
4. What is the purpose of a schema contract?
5. Why must reconciliation assumptions be documented?

5 Long Answer Questions

1. Explain how try and otherwise can be used in a controlled error-handling design.
2. Describe a systematic procedure for diagnosing a failed Power Query step.
3. Discuss how row counts, completeness, uniqueness, and reconciliations complement one another.
4. Design a repeatable testing strategy for a folder-combination query.
5. Explain the governance and privacy considerations associated with error and audit data.

3 Analytical / Application Questions

1. A query refreshes successfully, but output rows fall by 12% after a new date-conversion step. Develop an investigation plan and identify the evidence you would require before publishing the report.
2. A sales total agrees with the source total, but one store has no records and another is overstated. Explain why the reconciliation passed and propose stronger controls.
3. Design a control table for a monthly Power BI pipeline. Specify at least six fields, their purpose, and the escalation action for a failed control.

Multiple Choice Questions with Answers

Question 1

Which statement best distinguishes a silent failure from a hard failure?

A. A silent failure always stops the refresh.

B. A silent failure produces an incorrect result without necessarily stopping refresh.

C. A silent failure occurs only when a source file is missing.

D. A silent failure is the same as a null value.

Correct Answer: B. A silent failure produces an incorrect result without necessarily stopping refresh.

Question 2

What is the main purpose of using try in Power Query?

A. To encrypt a query.

B. To force every value to become text.

C. To capture the outcome of an expression and handle an error deliberately.

D. To remove all duplicate rows.

Correct Answer: C. To capture the outcome of an expression and handle an error deliberately.

Question 3

Which control most directly checks whether an expected source file is missing?

A. File inventory validation

B. Currency formatting

C. Column sorting

D. Visual theme testing

Correct Answer: A. File inventory validation

Question 4

If a source has 10,000 rows, output has 9,700 rows, and 200 exclusions are documented, what is the unexplained row difference?

A. 100 rows

B. 200 rows

C. 300 rows

D. 9,500 rows

Correct Answer: A. 100 rows

Question 5

Which condition makes the relative reconciliation difference formula unsuitable without a special rule?

A. The source total is positive.

B. The output contains dates.

C. The source total equals zero.

D. The data contains text columns.

Correct Answer: C. The source total equals zero.

Question 6

Why should an error often be retained in an exception table rather than simply removed?

A. To preserve traceability and support investigation.

B. To increase the number of dashboard visuals.

C. To avoid documenting assumptions.

D. To guarantee that all totals agree.

Correct Answer: A. To preserve traceability and support investigation.

Question 7

Which test checks whether a query change has damaged previously accepted behaviour?

A. Schema test

B. Regression test

C. Authentication test

D. Formatting test

Correct Answer: B. Regression test

Question 8

What is the primary risk of an overly broad otherwise null expression?

A. It can conceal important failures and create incomplete results.

B. It always causes a syntax error.

C. It prevents all transformations from running.

D. It automatically duplicates rows.

Correct Answer: A. It can conceal important failures and create incomplete results.

Question 9

Which issue is most likely indicated by a ‘column was not found’ message?

A. A schema change or an incorrect preceding transformation.

B. A successful reconciliation.

C. A valid currency conversion.

D. A documented tolerance.

Correct Answer: A. A schema change or an incorrect preceding transformation.

Question 10

Which item is essential for a repeatable test case?

A. A decorative dashboard image.

B. An undocumented personal judgement.

C. An input condition and an expected result.

D. A requirement to ignore boundary cases.

Correct Answer: C. An input condition and an expected result.