Power Query Errors: Causes, Meanings, and Fixes

A Power Query error means that a query step could not interpret, retrieve, or transform something it was given. In Excel for Microsoft 365 on Windows, open the query in Power Query Editor, inspect the error details and the step that produced it, then correct the source, data type, or transformation rather than simply hiding the error.

What a Power Query error means

Power Query is Excel’s data-import and transformation tool. It connects to sources such as workbooks, text files, databases, and online services, then records a sequence of steps for cleaning and reshaping the data. When you refresh a query, those steps run again against the current source. An error indicates that at least one step could not complete as written.

The error may affect one cell, an entire column, a step, or the final load into Excel. For example, a text value such as pending in a column Power Query is converting to a number can produce a cell error. A renamed source column can make a later step fail for the whole query. An expired sign-in can prevent the source from being read at all.

Power Query errors are not necessarily worksheet formula errors. They occur in the query before or during loading. A query can also refresh successfully but load to an unexpected place or produce incomplete-looking results; that is a separate issue from a transformation error and should be checked in the query’s load settings.

How to find the failing step

In Excel for Windows, choose Data > Queries & Connections, locate the query, right-click it, and choose Edit. In Power Query Editor, select the affected column or step. The Applied Steps list shows the operations in order; selecting steps one at a time helps identify where the preview first becomes an error. If a cell displays Error, select it to view the available error details, which often identify the value or operation that failed.

To isolate problematic rows, select the column and use Home > Keep Rows > Keep Errors. This filters the preview to rows containing errors in the selected column, making it easier to inspect the source values and their context. It is a diagnostic step, not necessarily the right final transformation: keeping only error rows would discard the other rows from the result. Remove the diagnostic step when you have finished investigating.

Read the message and note the step name, column, and value involved. A message about converting a value points toward type or formatting problems; one about a missing field points toward a changed source structure; and an authentication message points toward credentials or access. Exact wording can vary by connector and Excel build, so the underlying failure and the step that triggered it matter more than the label alone.

A practical troubleshooting sequence

Use a small, repeatable process instead of changing several steps at once. That makes it easier to tell whether a fix addresses the cause or merely moves the error elsewhere.

Refresh the query and record the full message. If only one query fails, investigate its source and steps; if several queries from the same service fail, check access, credentials, or service availability.
Open the query and select Applied Steps from top to bottom. Identify the first step where the preview changes from valid data to an error or an unexpected result.
Inspect the relevant source row, column heading, and value. Check for blanks, extra spaces, unexpected symbols, changed headers, or values that do not match the assumed format.
Correct the source or the transformation that makes the wrong assumption. For instance, adjust a type conversion, select the right header row, or update a step that refers to a renamed column.
Refresh again and verify both the preview and the loaded table. Check row counts, column names, and representative values rather than relying only on the absence of an error message.

If a preview looks correct but refresh fails, consider whether the source changed between preview and refresh, whether credentials expired, or whether the query relies on a local path that is unavailable in the current environment. A successful preview is useful evidence, but it does not guarantee that every later refresh will have the same source, permissions, or data.

Common cause: values do not match the data type

Type conversion is among the most common causes of cell-level errors. A column may appear numeric but contain an entry such as —, unknown, or a number stored with a different decimal or date convention. A date such as 04/05/2026 can also be ambiguous: depending on the expected locale, it may mean April 5 or May 4.

Check the conversion step, often named Changed Type, and inspect the values that fail. If the data uses a particular regional format, right-click the column and choose Change Type > Using Locale, then select the intended data type and locale. This is safer than assuming that every source uses the same date or decimal conventions. If symbols or explanatory words are genuinely part of the source, decide whether to clean them, preserve them as text, or represent the value as missing before converting.

Do not change an entire column to text just to make red error indicators disappear unless text is the correct meaning of the data. A text column may no longer sort, total, or participate in numeric calculations as expected after loading. Conversely, converting every value to a number can lose meaningful identifiers such as account codes with leading zeros. Choose the type according to how the field should be used, not merely how its values look.

Common cause: the source structure changed

Queries often contain steps that refer to exact column names, sheet names, table names, or file paths. If a colleague renames a heading, a source workbook adds a title row, or a file is moved, a step that once worked may no longer find the expected item. Errors involving a missing column or object usually call for checking the source and the step that navigates to or selects it.

Compare the current source with the version the query was designed for. Confirm that the right worksheet or table is selected, that headers are still in the expected row, and that required columns have not been renamed. Update the query to match the actual structure, or ask the source owner to restore a stable structure if many reports depend on it. Removing a failing step without understanding its purpose can leave later steps with incomplete or incorrectly shaped data.

Credentials, privacy, and unavailable sources

A query can fail before transformations begin if Excel cannot reach or authenticate to its source. Check whether a network location, database, or online service is available, whether your account still has permission, and whether the saved credentials need to be updated. In Excel for Windows, Data > Get Data > Data Source Settings provides controls for reviewing data-source permissions and credentials; the options shown depend on the connector.

When a query combines data from different sources, Power Query’s privacy settings can also affect how those sources are combined. Privacy levels help control whether data from one source can be passed to another during a query. If the error mentions Formula.Firewall, review which sources are combined and their privacy classifications. Set levels to reflect the real sensitivity and organizational policy of each source; do not lower privacy protections indiscriminately just to make a refresh proceed.

Some failures are temporary or outside the workbook, such as a service outage, a changed password, or a disconnected VPN. Retrying can help in those cases, but repeated retries will not repair a broken path, missing permission, or malformed transformation. Identify whether the failure happens while connecting, navigating the source, or processing its data before deciding what to change.

Replacing or handling errors safely

Power Query Editor includes Transform > Replace Errors for replacing error values in a selected column. Use it only when you have chosen a defensible replacement. Replacing a failed sales amount with zero, for example, treats an unknown amount as a real zero and can distort totals. Replacing it with null may better represent missing information, but it still means the original value could not be interpreted. Keep an audit trail or a separate exception query when those distinctions matter.

In a custom column, Power Query’s M language can handle a conversion failure explicitly. For example, if the current row has a field named Amount and the intended output is a number or null, the expression try Number.From([Amount]) otherwise null returns the converted value when possible and null when conversion raises an error. This does not tell you why conversion failed, so it is better suited to cases where null is an accepted outcome and the source exceptions are otherwise understood.

Another option is to remove error rows with Home > Remove Rows > Remove Errors. That is appropriate only when excluding those records is justified and the number and identity of removed rows are checked. For financial, compliance, or operational data, preserving and reviewing exceptions is often safer than silently dropping them.

When refresh works but the result is wrong

Not every Power Query problem produces an error message. A query may refresh without errors yet return too few rows because a filter excludes new values, or return unexpected values because a step uses the wrong header row or type. Review the Applied Steps and compare the final output with the source, especially after changing a filter, merge, grouping, or deduplication step.

Also distinguish a query result from its destination. In Excel, the Queries & Connections pane shows query status and load information; right-click a query to review its load options. A query set to create a connection only may not appear as a worksheet table. A query loaded to the Data Model will not appear as a normal table on a sheet. These can look like missing-data errors even though the transformation itself completed.

Preventing repeat errors

Reliable queries make their assumptions visible. Keep source headings consistent, use stable tables rather than changing ranges when possible, and avoid relying on a particular file location if files are routinely moved. Review automatic type-detection steps, especially when a source contains mixed values or dates in regional formats. When a source is under your control, agree on column names and data conventions with the people who maintain it.

Test the query with ordinary rows and known edge cases, including blanks, unusual dates, and text in numeric fields.
After changing a source or query, inspect row counts and key totals against a trusted reference.
Keep a record of deliberate replacements or removed rows so that data-quality decisions are explainable.
When sharing a workbook, confirm that intended users have access to the source and understand that credentials may be specific to their accounts.

Power Query features and connector support can differ across Excel for Windows, Excel for Mac, and Excel for the web. The ribbon paths above describe Excel for Microsoft 365 on Windows. If you use Mac or the web, look for the equivalent query-editing and data-source controls available in that version; a missing command or unsupported connector may require opening the workbook in Windows Excel.

Frequently Asked Questions

Why does Power Query say DataFormat.Error?
A value could not be interpreted in the expected format, often during a type conversion. Inspect the failing row and check for text, symbols, blanks, or locale differences in dates and numbers.

How do I find which row causes a Power Query error?
Open the query, select the affected column, and use Home > Keep Rows > Keep Errors. Inspect the remaining rows and error details, then remove the diagnostic filtering step when you are done.

Can I replace Power Query errors with blanks?
Yes. Use Transform > Replace Errors and choose a suitable replacement, such as null where missing data is acceptable. First consider whether the replacement could hide a bad value or affect calculations.

Why does a query work in the preview but fail on refresh?
The source, credentials, permissions, or available data may have changed, or the preview may not reveal a problem in every row. Check the refresh message, source access, and the first Applied Step that fails.

What does Formula.Firewall mean in Power Query?
It usually indicates that Power Query’s privacy rules are blocking or constraining how data from multiple sources is combined. Review the sources and their privacy levels, and follow your organization’s data-handling requirements.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.