Power Query Excel Error 5: What It Means and How to Fix It

Errors & fixes · Updated November 19, 2026

There is no single Microsoft Excel Power Query message called simply “Error 5.” The number may be a value that caused a conversion error, a Windows access-denied code from a data source, or an unrelated VBA error; check the complete message and its details before choosing a fix.

What “Error 5” can mean in Power Query

Power Query reports errors when it cannot complete a step in a query, but the useful information is usually the error type, message, and details—not the number on its own. For example, a cell may show Error because the query tried to convert the value 5 to a date, or because a value has a format the selected data type does not accept. In that case, 5 is part of the input or the details, not a universal Power Query error code.

A different possibility is Windows system error code 5, which means access is denied. A connector may surface an access problem when Excel cannot read a file, folder, network location, or other source. The exact wording depends on the connector and circumstances, so do not assume every message containing the number 5 means access is denied.

There is also a commonly confused message: VBA Runtime error 5, “Invalid procedure call or argument.” That is a Visual Basic for Applications error, not a Power Query error. It can occur in a macro that refreshes or works with a workbook, but it needs to be diagnosed in the VBA code rather than by changing a query’s data types.

How to find the complete message

In Excel for Microsoft 365 on Windows, open Data > Queries & Connections, then double-click the relevant query to open Power Query Editor. Refresh the preview if needed, locate a cell marked Error, and select it to inspect the error details. The display varies slightly by Excel build, but the details should help identify the failing step and explain what Power Query could not do.

If errors are buried in a large preview, use Home > Keep Rows > Keep Errors to temporarily show only rows containing errors. This is a useful way to investigate affected records; it changes the query steps, so remove the resulting Keep Errors step afterward if you want the full dataset in the final output. You can also select a step in the Applied Steps list and check whether the error first appears there or in a later step.

Record these details before changing anything:

  • The complete error message and any displayed reason or detail.
  • The query name, affected column, and Applied Step where the error first appears.
  • Whether the problem affects every row, a few values, or the whole source connection.
  • Whether it began after a file was moved, a column changed, credentials expired, or a query was edited.

Power Query’s View tab includes data profiling options such as Column quality, which can help reveal whether a column contains errors. Profiling is a diagnostic aid, not proof that every source row is clean; check the profiling scope and inspect the actual error rows when accuracy matters.

Common causes and what the details tell you

A frequent cause is a data-type mismatch. Imagine an Amount column containing 12.50, 18.00, and the text “pending.” If a Changed Type step converts the column to a number, the text entry cannot be converted and may become an error. A similar problem occurs when a date column mixes dates with labels such as “unknown,” or when numbers use a decimal or date format that does not match the conversion settings.

Errors can also arise because a later step expects a column or object that no longer exists. A source file may have been redesigned, a header renamed, or a sheet removed. In those cases, look for messages referring to a missing column, key, field, or item, and review steps such as Renamed Columns, Removed Columns, and Navigation. Fixing the source structure or updating the affected step is more reliable than suppressing the error.

For an access-denied message, check whether the signed-in user can open the source directly and whether the file or folder is available at the same path used by the query. A network drive may be disconnected, permissions may have changed, or the query may be using outdated credentials. In Excel, review Data > Get Data > Data Source Settings for the source’s permissions or credentials. Do not put passwords into query text or share them in a workbook.

Other errors can come from a missing or moved file, an unavailable server, a changed web page, privacy-level restrictions, or a connector-specific limitation. These problems are not fixed by converting a column to text. Read the message for clues about whether the failure occurred while connecting to the source, navigating within it, or transforming values.

A practical way to diagnose and fix the error

  1. Open the query and inspect the error. Read the complete message and note the step named in the details. If the message is about access, investigate the source and permissions first; if it describes a conversion, inspect the affected values.
  2. Find the first failing step. Select Applied Steps from top to bottom. The step where the preview changes from valid data to errors is often the step that needs attention. Earlier steps may simply provide the input that exposed the issue.
  3. Check the source values and column headers. Compare an error row with a normal row. Look for blanks, text mixed with numbers, unexpected symbols, changed headers, or values with a different date or regional format.
  4. Correct the cause where possible. Repair the source data, restore the expected column, reconnect to the correct file, or update credentials. For conversion issues, select the right column and use Transform > Data Type to choose an appropriate type. When interpreting dates or decimal numbers, use the correct locale if the source uses a different regional format.
  5. Refresh and verify. Use Home > Close & Load to return to Excel when the query is ready, then refresh it and check both the output and any error rows. A query preview can look correct while a later refresh encounters new source data, so validate after refreshing.

Changing a column to Text can be a reasonable choice when the values are identifiers, codes, or mixed labels rather than numbers for calculation. It is not a universal cure: it may hide a data-quality issue or prevent later arithmetic. Choose a type that reflects what the field means, not merely one that removes the visible error.

When replacing or keeping errors is appropriate

Sometimes an error is expected and there is a defined business rule for handling it. For instance, if a source uses “pending” in a numeric field, you might keep it as text in a separate status column or replace it with a blank only when a blank genuinely represents the intended result. In Power Query Editor, Transform > Replace Errors can replace errors in a selected column. Use a replacement value that is valid for that column’s data type.

For a more controlled transformation, Power Query’s M language supports try expressions. For example, with a column named Amount containing values that should be numeric, try Number.From([Amount]) otherwise null attempts conversion and returns null if conversion fails. This is useful only if treating invalid values as null is acceptable. Otherwise, the query can silently discard information that should be corrected or reported.

Another diagnostic option is to preserve errors and filter them into a separate review query or output. This lets a normal result load while still giving someone a list of records that need attention. Avoid a blanket “remove errors” step unless you have confirmed that dropping those records is acceptable; the missing rows may affect totals, reports, or downstream decisions.

How Excel and Power Query handle errors

Power Query transforms data before it is loaded into a worksheet or the Data Model. Its errors belong to the query transformation process and are distinct from worksheet errors such as #VALUE! or #N/A, although a loaded result can ultimately display an error-related outcome. A worksheet formula fix will not necessarily repair the query step that failed, and changing the query may be necessary before the refreshed output is correct.

Power Query’s M language evaluates steps in sequence. A step can succeed for most rows but fail for one value, and later steps can expose problems that were not obvious earlier. The error details often identify the value or operation involved. That is why finding the first failing step is more useful than repeatedly refreshing or changing unrelated settings.

The directions above are for Excel for Microsoft 365 on Windows. Power Query is also available in supported Microsoft 365 editions for Mac, but connector availability and editing features can differ, and the ribbon may not match the Windows paths exactly. Excel for the web has more limited query authoring and refresh support than the Windows desktop app. If a menu or connector is missing, confirm the capabilities of your Excel platform and version rather than assuming the query is corrupt.

Preventing the same problem on future refreshes

Queries are more dependable when source files have stable names, column headers, and data types. If people enter data manually, use clear rules for what belongs in each column and keep status text separate from numeric values. Where a source legitimately contains exceptions, decide in advance whether to flag, preserve, or replace them, and document the rule in the query.

  • Keep the expected source file and folder path stable, or update the query when they change.
  • Check credentials and access when a source is on a network, website, or shared service.
  • Inspect refresh results after a source schema or data-entry process changes.
  • Retain a way to review invalid records instead of silently discarding them.
  • Save a copy of the workbook before making extensive query edits, especially when the query feeds reports used by others.

If the query worked before and now fails without a deliberate edit, compare the current source with the previous version. A new column value, changed header, renamed sheet, or permission update can explain the change more quickly than rebuilding the entire query.

Frequently Asked Questions

Does Power Query have a standard Error 5 code?
No. “Error 5” by itself is not enough to identify a standard Power Query failure. Read the full message and details; the number could be a data value, part of a connector or system message, or a reference to a different kind of error.

What does access denied, error 5 mean in Excel?
Windows system error code 5 means access is denied. If it appears during a query, check that you can access the source, that its path is correct, and that the credentials or permissions are current. Connector messages can vary, so use the full details to confirm the cause.

How do I find which Power Query step caused an error?
Open the query in Power Query Editor and select the Applied Steps one at a time. Find the earliest step where the preview shows an error, then inspect the error cell and the input values for that step.

Should I replace Power Query errors with null?
Only when null is a truthful and acceptable representation of the missing or invalid value. If the error indicates a source change or a meaningful bad record, preserve it for correction or review instead of hiding it.

Is VBA Runtime error 5 the same as a Power Query error?
No. VBA Runtime error 5 is an error raised by VBA code, often described as an invalid procedure call or argument. It may occur in a macro that interacts with Excel, but it is separate from an error inside a Power Query transformation.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.