If you mean Excel’s ERROR.TYPE result 5, it identifies the #NAME? error. Power Query does not assign errors that same numbered code: it represents them as error records, which you can inspect, catch, or replace in the query editor.
What Error 5 Means in Excel
Excel’s ERROR.TYPE worksheet function returns a number that corresponds to a recognized Excel error value. The number 5 means the value being checked is #NAME?. It is not a general error count, an error severity rating, or a Power Query error number.
A #NAME? error usually means Excel cannot recognize text in a formula as a valid function, name, or reference. For example, a misspelled function, a reference to a name that was never defined, or text that should have quotation marks can cause it. Fixing the underlying formula is different from identifying the error with ERROR.TYPE.
Other familiar ERROR.TYPE results include 2 for #DIV/0!, 3 for #VALUE!, 4 for #REF!, 6 for #NUM!, and 7 for #N/A. These numbers are useful as a worksheet classification, but they should not be treated as universal identifiers for errors in other Excel features.
Why Power Query Does Not Use Error Type 5
Power Query uses the M language, whose errors have structured information rather than the numbered categories returned by Excel’s worksheet function. An error can include a reason, a message, and optional detail. Examples of reason labels include DataFormat.Error and Expression.Error; the exact message and detail depend on what failed.
That distinction matters because the same underlying problem can appear differently in the two environments. A worksheet formula might display #NAME? and produce 5 through ERROR.TYPE. A Power Query step that refers to an unknown field or cannot convert text to a number may instead show a message in the editor. There is no built-in mapping that says every Power Query error with a certain message is Excel error type 5.
Power Query can also encounter errors before data reaches a worksheet, such as when it reads a source file or applies a transformation. Conversely, a formula error may occur in a worksheet after query results have loaded. Identify which environment is showing the error before choosing a repair.
Check for Error Type 5 in a Worksheet
To classify a worksheet error, enter ERROR.TYPE in a cell that points to the cell containing the error. With an Excel error value already in A2, use:
=ERROR.TYPE(A2)
If A2 contains #NAME?, the formula returns 5. If A2 contains a different recognized Excel error, the result is its corresponding error number. If the referenced cell does not contain an error, ERROR.TYPE returns #N/A; that result does not mean the original cell contained #N/A.
In Microsoft 365 for Windows, enter the formula in a blank cell and press Enter. On a Mac, the formula is the same; the interface and some keyboard shortcuts differ, but there is no special Mac version of ERROR.TYPE. If you see 5, inspect the original formula in A2 rather than treating 5 as the cause. Check for spelling, missing quotation marks around literal text, and misspelled or absent defined names.
How Power Query Represents Errors
In Power Query, an error is a value that can be examined by a query step. The M expression try lets you evaluate an operation without immediately stopping the query. On success, it returns a record containing a Value field; on failure, it returns a record with HasError set to true and an Error field containing error information.
For example, suppose a query has a column named AmountText containing values that should be numbers. This M expression adds a column that records the result of attempting the conversion:
Table.AddColumn(Source, "AmountCheck", each try Number.From([AmountText]))
This assumes the previous query step is named Source and each row has an AmountText field. The added column contains a try-result record for each row. You can expand or inspect those records to distinguish successful conversions from failures, rather than relying on an Excel error number.
For a simpler cleanup where invalid values should become null, use an expression like this in a custom column:
each try Number.From([AmountText]) otherwise null
The expression returns the converted number when conversion succeeds and null when it fails. This is convenient, but it hides the reason for each failed conversion unless you retain a separate diagnostic column. For audit-sensitive or quality-checking work, inspect and report errors before replacing them.
Inspect and Handle Errors in the Query Editor
For Excel for Microsoft 365 on Windows, open a query from Data > Queries & Connections, then double-click the query to open Power Query Editor. A refresh or preview may identify a failing step, and cells in the affected column can show an error. Select an error cell to view its details; the message can point to a conversion, missing field, or other failed operation.
To isolate problematic rows, select the relevant column and use Home > Keep Rows > Keep Errors. This is useful for diagnosis because the remaining preview focuses on rows that failed in that column. When the issue is understood, remove the diagnostic filter or adjust the transformation. To discard errors intentionally, Home > Remove Rows > Remove Errors removes rows with errors in the selected column. Removing rows is a data decision, not merely a way to make the warning disappear.
To replace errors in a particular column, select that column and use Transform > Replace Errors, then enter the replacement value. Choose a replacement that makes sense for the data type and downstream calculations. For example, replacing a failed numeric conversion with zero may incorrectly increase totals or imply a real zero measurement. Null or a separate status field may be more appropriate.
Power Query’s Windows ribbon labels and available connectors can vary by Excel build. Excel for Mac has Power Query capabilities, but the supported sources and authoring features may differ by version; follow the commands available in that release rather than assuming every Windows menu item is present. Excel for the web also does not provide the full desktop Power Query editing experience in every scenario.
Common Causes and Practical Fixes
Power Query errors often arise because a transformation assumes the source is more consistent than it really is. A column may contain numbers on most rows but also a dash, a blank, a word such as “unknown,” or a value formatted differently in a new file. Applying a numeric data type then fails on incompatible entries.
- Conversion failure: Inspect the error detail and the original source value. Clean unexpected symbols or text, or use a conversion approach that handles the actual source format.
- Missing or renamed column: Check whether the source headers changed. Update the step that refers to the old column name, or stabilize the source headers if you control them.
- Changed source structure: A file may have an extra header row, a different sheet name, or a different table layout. Verify the navigation and header-promotion steps.
- Unexpected nulls or blanks: Decide whether they mean missing information, zero, or an invalid record. Those meanings are not interchangeable.
- Formula or name problem in Excel: If the worksheet—not the query—shows
#NAME?, check the formula text and defined names.ERROR.TYPEcan confirm the category, but does not repair it.
When a query refresh fails, look at the applied steps in order. The first step that introduces errors is often more informative than the last step that displays them. A type-conversion step may reveal a dirty source value, while a later merge or calculation may merely carry the error forward.
Choosing Between Error.TYPE and Power Query Error Handling
Use ERROR.TYPE when an Excel worksheet already contains an error value and you need to classify it inside a formula. It is particularly useful in a report that groups formula errors or flags cells for review. It does not inspect a Power Query error record or turn a query error into a numbered category.
Use Power Query’s error tools when the problem occurs during data import or transformation. Keep errors while investigating, replace them only when the replacement has a clear meaning, and remove rows only when excluding those records is justified. If both a query and a worksheet are involved, diagnose them separately: first confirm whether the query loaded the expected data, then inspect formulas that use the loaded result.
A common misconception is that “error 5” means the fifth error row in a query. It does not. In Excel’s ERROR.TYPE system, 5 specifically means #NAME?. Power Query’s error records do not share that numbering system.
Frequently Asked Questions
What does ERROR.TYPE 5 mean in Excel?
It means the checked cell contains the #NAME? error. The number classifies the worksheet error; it does not identify the exact typo or missing name that caused it.
Is there an Error.Type function in Power Query?
No. Power Query uses M error values and records, not Excel’s numbered ERROR.TYPE categories. Use try to capture a result and inspect its HasError, Value, or Error fields.
Does Power Query error 5 mean #NAME?
Not by itself. The number 5 has that meaning when returned by Excel’s ERROR.TYPE function. A number shown in a Power Query message or source system is not automatically the same classification.
How do I find the rows causing a Power Query error?
Select the column that displays the error and use Home > Keep Rows > Keep Errors in Power Query Editor. Inspect the error details and source values, then correct the transformation or handle the affected rows deliberately.
Should I replace Power Query errors with zero?
Only if zero accurately represents the missing or invalid value for your analysis. Otherwise, zero can distort totals and averages. Consider retaining an error status, using null, or fixing the source data instead.
Related
- Comparison Table Excel Template: Build, Format, and Use
- Comparison Excel Sheet Template: Build a Clear Side-by-Side
- Comparison Plot in Excel With R²: Steps and Meaning
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Function Not Showing Result? Fix the FormulaIf an Excel function is not showing a result, first check whether Excel is displaying formulas, treating the…
- Excel Dropdown List Error? Causes and FixesAn Excel dropdown list error usually comes from a missing or incorrect Data Validation source, a disabled in-cell…
- IRR Formula Excel #NUM! Error: Causes and FixesIn Excel, =IRR(A2:A5) returns #NUM! when Excel cannot find an internal rate of return for the cash flows…
- Excel Merged Cells Not Wrapping Text? Fix the LayoutIf text in an Excel merged cell is not wrapping, select the merged cell and choose Home >…
- IFERROR Not Working in Excel? Find and Fix the CauseIf IFERROR is not working in Excel, first check that the formula is written as =IFERROR(your_formula, value_to_show_if_an_error_occurs) and…
- Excel Cell Pull-Down Lists: Create, Edit, and Fix ThemTo create an Excel cell pull-down list in Microsoft 365 for Windows, select the cell, choose Data >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.