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?

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.

(input as table) => 

let

    Source = input,

    #"Kept Errors" = Table.SelectRowsWithErrors(Source, Table.ColumnNames(input)),

    #"Added Custom" = Table.AddColumn(#"Kept Errors", "AllItems", each _),

    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Record.ToTable([AllItems])),

    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom"}),

    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Value"}, {"Name", "Value"}),

    #"Added Custom2" = Table.AddColumn(#"Expanded Custom", "Custom", each try [Value]),

    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Added Custom2", "Custom", {"Error"}, {"Error"}),

    #"Expanded Error" = Table.ExpandRecordColumn(#"Expanded Custom1", "Error", {"Reason", "Message", "Detail", "Message.Format", "Message.Parameters", "ErrorCode"}, {"Reason", "Message", "Detail", "Message.Format", "Message.Parameters", "ErrorCode"}),

    #"Filtered Rows" = Table.SelectRows(#"Expanded Error", each [Reason] <> null and [Reason] <> "")

in

    #"Filtered Rows"

• 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