Power BI Error Handling in Power Query

Errors in Power Query are messages about your data. Some stop a refresh outright; others quietly turn individual cells into "Error". The tempting fix is to remove them and move on, which is sometimes right and sometimes deletes exactly the data the report needs. This guide covers the two kinds of error, the three built-in ways to handle cell errors, custom logic with try, and a real example of how a routine type change can create thousands of errors.

Two kinds of error

Microsoft distinguishes them clearly (Microsoft Learn: dealing with errors):

• Step-level errors stop the query from loading and show a yellow error bar with a reason, message and detail. Common examples are DataSource.NotFound, when a file has moved or the user lacks access, and "The column ... of the table wasn't found", when a source column was renamed. Formula.Firewall errors appear when combining sources with incompatible privacy levels.
• Cell-level errors let the query load but show "Error" in individual cells. The most common cause is a data type conversion that fails, such as text "NA" in a column changed to Whole Number. Unsupported operations, such as combining text and a number without converting first, are another.

Step-level errors must be fixed at their cause: the path, the credentials, or the step that references a missing column. Microsoft's own example is a query that reads a file from one person's drive D and fails for a colleague who has no drive D; shared queries should read from locations everyone can reach. Cell-level errors need a decision about each one.

Finding cell errors

Turn on Column quality in the View tab of Power Query Editor. It shows the percentage of valid, error and empty values in each column, based on the preview rows by default; switch profiling to the entire data set when the preview might not contain the problems. Select the white space in an error cell to see its message.

How a routine type change creates errors, in numbers

Setting data types is one of the first steps in any query, and codes that look numeric are easy to mistype. Here is what would happen if a UK online retailer's invoice numbers and stock codes (UCI: Online Retail) were changed to Whole Number:

import io
import urllib.request
import zipfile
import pandas as pd

url = "https://archive.ics.uci.edu/static/public/352/online+retail.zip"
with zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())) as archive:
    raw = pd.read_excel(archive.open("Online Retail.xlsx"), dtype={"InvoiceNo": str, "StockCode": str})
revenue = raw["Quantity"] * raw["UnitPrice"]
for column in ["InvoiceNo", "StockCode"]:
    as_number = pd.to_numeric(raw[column], errors="coerce")      # what "Change type to Whole Number" would attempt
    errors = raw.loc[as_number.isna(), column]
    print(f"{column}: {len(errors)} conversion errors ({len(errors) / len(raw):.1%} of rows, "
          f"{revenue[as_number.isna()].sum() / revenue.sum():.1%} of revenue)")
    print("   most common failing values:", ", ".join(errors.value_counts().head(4).index))

InvoiceNo: 9291 conversion errors (1.7% of rows, -9.3% of revenue)
   most common failing values: C570867, C560540, C548460, C560855
StockCode: 54873 conversion errors (10.1% of rows, 8.5% of revenue)
   most common failing values: 85123A, 85099B, POST, 85099C

failing_codes = raw.loc[pd.to_numeric(raw["StockCode"], errors="coerce").isna(), "StockCode"]
print(f"failing stock codes that are digits plus a letter suffix: {failing_codes.str.match(r'^[0-9]+[A-Za-z]+$').mean():.1%}")
failing_invoices = raw.loc[pd.to_numeric(raw["InvoiceNo"], errors="coerce").isna(), "InvoiceNo"]
print("failing invoice numbers by first letter:", failing_invoices.str[0].value_counts().to_dict())

failing stock codes that are digits plus a letter suffix: 94.5%
failing invoice numbers by first letter: {'C': 9288, 'A': 3}

These are not bad data. The failing invoice numbers are almost all cancellations, marked with a "C" prefix, whose negative quantities offset 9.3% of revenue. The failing stock codes are overwhelmingly product variants such as 85123A. Clicking Remove errors after either type change would silently delete every cancellation, overstating sales, or a tenth of all product lines. The correct fix is not error handling at all: these columns are identifiers and should stay as text.

The three built-in options

When errors are genuine, Power Query offers three operations, each from the column's menu or the ribbon:

Microsoft suggests Keep errors as an auditing tool. A useful habit is to create a reference query that keeps errors, load it to a hidden table or a separate report page, and check its row count after each refresh.

Custom logic with try, otherwise and catch

Power Query's equivalent of Excel's IFERROR is try ... otherwise (Microsoft Learn: error handling). In a custom column, it returns the value if the expression succeeds and the alternative if it fails:

try [Standard Rate] otherwise [Special Rate]

For more control, try on its own returns a record with HasError, Value and Error fields, and the error record contains the reason, message and detail. The catch keyword applies logic to that record directly, for example using a fallback only for certain errors:

try [Standard Rate] catch (r) => if r[Message] <> "Invalid cell value '#REF!'." then [Special Rate] else null

A pattern that keeps problems visible is to add a flag column rather than hiding the error:

Table.AddColumn(Source, "Price check", each if (try Number.From([Price]))[HasError] then "Invalid price" else "OK")

The flag can then be counted in a report, so errors become a data quality measure rather than a silent loss.

A policy for errors

1. Fix causes first: wrong types, moved files and renamed columns.
2. Count before you remove. Know how many rows and how much value an operation affects.
3. Prefer flags to deletion, so problems remain measurable.
4. Document any replacement value in the step name, such as "Replaced price errors with 0 (free samples)".
5. Re-check after refresh: new source data brings new errors.

Data cleaning, step by step applies the same count-what-you-remove discipline outside Power BI, and the Power Query tutorial covers the transformations these steps sit among. When the same queries serve several models, handling errors once in a dataflow keeps the rules consistent.

Data cleaning techniques and handling missing values in Power Query are part of the data transformation module of the Power BI course.

Sources

• Dealing with errors in Power Query (Microsoft Learn) — Microsoft Learn
• Error handling in Power Query (Microsoft Learn) — Microsoft Learn
• Online Retail (UCI Machine Learning Repository) — UCI Machine Learning Repository