Power Query: Custom Function for Error Detection in Tables

When you're transforming data in Power BI or Excel using Power Query, you're bound to run into errors—mismatched data types, division by text, nulls, you name it. But instead of manually inspecting row by row, why not automate error detection with a custom Power Query function?

---

Check out some of our FREE templates and materials.

Join newsletter

PRACTICE MATERIAL BELOW!👇

---

In this blog post, I’ll walk you through a clean method to catch and analyze all row-level errors using Power Query. This approach works great when importing or transforming large datasets.

We’ll cover:

• ✅ Why this is useful

• 🧠 What this M function does

• 🧰 Step-by-step example

• 🖼️ Visual illustrations for context

Why Catching Errors Matters

Imagine loading thousands of rows—only to find your dashboard missing values or blowing up visuals. You check the source and—bam—an error like this pops up:

“We cannot apply operator / to types Text and Number.”

That’s what happened to me recently while loading a dataset in Power BI.

This warning tells us 15,000 rows failed to load due to type mismatch.

To investigate deeper, I used a small Power Query function that:

• Grabs all error rows from a table

• Extracts detailed error metadata

• Helps me fix root causes systematically
  

Let’s dive in.

The Power Query Custom Function

Here's the M code for a reusable error extraction function. Save it as a blank query and name it something like fnDetectErrors.

// fnDetectErrors

// Returns one row per error cell with details.

// Usage: fnDetectErrors(MyTable) or fnDetectErrors(MyTable, {"ColA","ColB"})

let

fnDetectErrors = (input as table, optional columns as nullable list) as table =>

let

// Add original row index first (so it references the source table)

WithIndex = Table.AddIndexColumn(input, "RowIndex", 0, 1, Int64.Type),

// Decide which columns to check (all by default), and validate they exist

ExistingCols = Table.ColumnNames(WithIndex),

ColsRequested =

if columns <> null and List.Count(columns) > 0 then columns else ExistingCols,

ColsToCheck = List.Intersect({ExistingCols, ColsRequested}),

// Keep only rows that contain at least one error in those columns

KeptErrors = Table.SelectRowsWithErrors(WithIndex, ColsToCheck),

// Convert each row to a [Name, Value] table (one row per field/cell)

WithKV = Table.AddColumn(KeptErrors, "KV", each Record.ToTable(_)),

ExpandedKV =

Table.ExpandTableColumn(

Table.SelectColumns(WithKV, {"RowIndex", "KV"}),

"KV",

{"Name", "Value"},

{"Column", "CellValue"}

),

// Keep only error cells and extract the error record

OnlyErrorCells = Table.SelectRows(ExpandedKV, each Value.Is([CellValue], type error)),

WithError =

Table.AddColumn(

OnlyErrorCells,

"Error",

each try [CellValue] otherwise null

),

ExpandedError =

Table.ExpandRecordColumn(

WithError,

"Error",

{"Reason", "Message", "Detail", "Message.Format", "Message.Parameters", "ErrorCode"},

{"Reason", "Message", "Detail", "Message.Format", "Message.Parameters", "ErrorCode"}

),

// Final, readable output

Final =

Table.SelectColumns(

ExpandedError,

{"RowIndex","Column","Reason","Message","Detail","ErrorCode","Message.Format","Message.Parameters","CellValue"}

)

in

Final

in

fnDetectErrors

• How to Use This Function

Let’s say you have a table called chem_Sales and want to find rows with data issues (like dividing a number by text). You can:

1. Load chem_Sales in Power Query.

2. Create a new blank query and paste the function above.

3. Call the function with:

= fnDetectErrors(chem_Sales)

Boom 💥 — all error rows pop up!

•  Understanding the Output

Once you invoke the function, the result looks something like this:

A filtered table of errors with fields like.

• Name (column name where error occurred)

• Value (shows “Error”)

• Reason (e.g., Expression.Error)

• Message (specific explanation, e.g., can't divide text by number)

• Message.Format (parameterized message)

• ErrorCode (numeric error code, e.g., 10051)

This gives you full insight into which columns and what types of issues triggered the error.

🔎 In the screenshot above, the function found many rows where Power Query tried to divide a text value in Discount_Percent column. You can't divide text, hence the failure.

• End Result in the Report

After identifying and resolving the issue (by cleaning the column types), the fixed query loads like this:

You now see clean data in your visual, and the error summary confirms the type mismatch has been diagnosed.

• Tips and Good Practices

  
  

Here are some smart ways to integrate this method in real projects:

• 🔁 Use fnDetectErrors() before every load in development phase.

• 🧪 Combine with a type detection step (Table.DetectType) to proactively cast columns.

• 💬 Log your errors to a separate report or table for future debugging.

• 🚫 Don’t just “Remove Errors” blindly — always investigate root causes.

• When to Use

This custom error function is helpful for:

• Debugging custom column calculations

• Tracking down silent data transformation issues

• Auditing imported datasets (e.g., CRM, finance, chemical data logs)

• Teaching teams how Power Query errors behave

Final Thoughts

Power Query gives you serious control over data quality, and this function is one of the most overlooked tools in your toolbox. Instead of guessing why values disappeared or visuals broke, now you can trace errors down to their row, column, and message.

Just plug and play.

You can download M-code here for free!

pq_errordetection_2025.txt

Download TXT • 1KB

---

Do you need a Power Query Calendar function for your Excel and Power BI Projects? Check it out.

Advanced Calendar (reusable function)

€5.00

Buy Now