To check for errors in Excel Power Query, open the query in Power Query Editor and use View > Column quality or Home > Keep Rows > Keep Errors to locate problem rows. You can then inspect the error details, correct the source or transformation, or deliberately replace the error; there is no single setting called “error checking” that automatically fixes every issue.
What error checking means in Power Query
Power Query is Excel’s tool for importing, cleaning, and reshaping data. It records each transformation as an applied step, then repeats those steps when the query refreshes. An error can therefore come from the source data, from a conversion or calculation in a step, or from a mismatch between the query and a changed source. Finding the row is only the first part of checking it: you also need to decide whether the value is invalid, missing, or simply different from what the query expected.
Power Query errors are not the same as worksheet errors such as #VALUE! or #N/A, although a query may load error values into a worksheet. In the editor, an error cell often displays the word Error. Selecting it can show details, including the message and the step where the issue occurred. Error messages are clues, not always a complete explanation: for example, a conversion error tells you a value could not be converted, but you still need to inspect that value and decide what it should mean.
The five useful checks are column profiling, keeping only error rows, inspecting individual error details, replacing errors when that is appropriate, and validating values before applying later transformations. Used together, these methods help you distinguish a real data-quality problem from an overly strict query step.
Five ways to find and examine errors
- Turn on column profiling. In Power Query Editor, open the View tab and select Column quality. The quality indicator summarizes valid, error, and empty values for each column. Column distribution and Column profile can provide additional context, such as distinct values and statistics. Profiling is a way to spot patterns, not a guarantee that every row has been checked: the editor may profile only the top 1,000 rows by default. Use the profiling scope control at the bottom of the editor to choose the entire data set when you need a full check.
- Filter to error rows. Select the column that may contain errors, then choose Home > Keep Rows > Keep Errors. The resulting step keeps rows with errors in the selected column so you can examine them without scrolling through the full preview. This changes the query’s current result, so use it as a temporary diagnostic step or remove it when you have finished investigating. It does not repair the data.
- Read the error details. Select an Error cell in the preview to see its details. Note both the message and the column. If the error appears only after a particular step, select earlier steps in the Applied Steps pane to find where the value first becomes an error. This narrows the cause: a value might be text before a Changed Type step and an error after Power Query tries to convert it to a number.
- Replace errors selectively. Right-click the affected column and choose Replace Errors, or use the corresponding command on the Transform tab when it is available. Replacing an error with null or another value can let the query continue, but it can also conceal a source problem. Choose a replacement that has a clear meaning for the analysis, and document the decision when other people rely on the query.
- Check the data before conversion. Inspect the source values and the step that changes their type. If a column is supposed to contain numbers, check for text such as N/A, spaces, currency symbols, or regional number formats before converting it. Correcting the source or adjusting the conversion rules is usually better than replacing every resulting error with a default value.
A practical workflow in Excel for Microsoft 365
On Windows, select a cell in the loaded query table and choose Data > Queries & Connections. Double-click the query to open it in Power Query Editor. If you are starting from a source instead, use Data > Get Data and select the appropriate source; the exact import option depends on where the data is stored. In the editor, follow a short, repeatable investigation rather than changing several steps at once:
- Identify the column with suspicious values using Column quality or by scanning the preview. If the source is large, confirm whether the profile covers the full data set.
- Select the step immediately before the first step that shows errors. Compare the values there with the values after that step.
- Use Keep Errors on the affected column to isolate the rows. Record a few example values and read the error details.
- Decide whether the source needs correction, the transformation needs a different rule, or the row should intentionally be excluded or assigned a replacement.
- Remove any temporary Keep Errors step, then review the final preview and refresh the query to check that the result is still correct.
For example, suppose an Amount column contains 125, 80, and the text N/A. A Changed Type step that converts the column to a number may produce an error for N/A. The error is not evidence that the number 125 was calculated incorrectly; it indicates that the text value does not fit the conversion. You might correct N/A in the source, retain it as a missing value under an explicit rule, or flag the row for review. Replacing it with 0 would assert that the amount was zero, which is different from saying the amount is unknown.
Handle errors without hiding important data
The right treatment depends on what an error represents. If the source contains a typo, correct the source when you can and refresh. If a field is genuinely optional, a null value may be appropriate. If the row is invalid for the task, filtering it out may be justified, but keep a record of the exclusion and its effect. If an unexpected value needs investigation, preserve or flag it rather than silently substituting a plausible-looking value.
Power Query also supports a try expression in the M language. For instance, in a Custom Column expression, try Number.From([Amount]) otherwise null attempts to convert Amount to a number and returns null if the attempt fails. This can be useful when mixed input is expected, but it treats every conversion failure the same way. It does not tell you whether the original value was a typo, a label such as N/A, or a number written in an unfamiliar format. Keep the original column or add a separate status column if you need to audit which rows failed. Do not use a fallback merely to make the query refresh successfully.
When a column contains numbers formatted differently by region, check the conversion step’s locale options rather than assuming the value is invalid. For example, punctuation can have different decimal and thousands-separator meanings in different locales. In Power Query, the type-conversion commands offer a Using Locale option in supported conversion menus. Select the locale that matches the source data, then inspect the results before loading. Changing the locale can affect every value in that conversion, so verify both ordinary and edge-case entries.
Common causes and what their messages suggest
- Changed Type errors: A source column mixes numbers and text, or contains values that cannot be converted to the selected type. Look at the original value and decide whether the type or input needs adjustment.
- Missing field or column errors: The source layout may have changed, a header may have been renamed, or a prior step may have removed a column that a later step expects. Compare the current source headers with the query steps.
- Formula or custom-column errors: A calculation may receive a null, text, or an unexpected value. Check the inputs and the logic at the step where the error begins.
- File or source errors: A file may have moved, access may have changed, or a workbook, folder, or database may not be reachable. These are different from a bad value in a row; check the source location and permissions.
- Errors after a source update: New rows may use a format or category that was not present when the query was first built. Compare recent records with earlier ones and test the query against representative new data.
Do not assume that a query is error-free just because the preview looks clean. A preview and its profiling indicators can be limited to a sample, while a refresh processes the full source. Conversely, a refresh failure may be a connection or permission problem rather than a cell-level data error. Identify whether the failure is in a row, a transformation, or the source connection before choosing a fix.
Check the result after fixing the query
After changing a step, inspect more than the rows that originally failed. Confirm that the affected column has the intended data type, that valid values remain unchanged, and that totals or row counts still make sense for the task. If you changed an error to null, check how later steps treat nulls; a grouping, filter, or calculation may handle them differently from zero or an empty text value.
Then select Home > Close & Load to return the query result to Excel, or use Close & Load To when you need to choose a destination. Refresh from Data > Refresh All, especially after correcting or updating the source. If an error returns only on refresh, note the exact message and check whether the incoming data has changed since the preview. A query should be checked with representative current data, not only the sample that existed when it was first created.
Power Query error checking versus worksheet checks
Power Query is best for finding and handling issues during the import and transformation process. Excel worksheet tools are useful for checking the loaded result or for investigating values after they have been placed in cells. For example, filters, conditional formatting, and worksheet formulas can help flag unexpected blanks or values outside an expected range. Those checks do not replace examining a transformation that fails before data loads, and worksheet errors are not automatically identical to Power Query errors.
Another common misconception is that Replace Errors is the same as correcting the source. It changes the query result at that step; it does not necessarily alter the original file or database. If the source remains unchanged, the original value can reappear on a later refresh and be replaced again. That may be a deliberate rule, but the transformation should make its purpose clear to anyone maintaining the workbook.
Frequently Asked Questions
Where is the Keep Errors command in Excel Power Query? In Power Query Editor, select the column, then choose Home > Keep Rows > Keep Errors. It filters the preview and query result to rows containing errors in that column; remove the diagnostic step when you no longer need it.
Why does Power Query show an error when Excel displays a value? A value may look acceptable in a cell but fail a Power Query conversion or transformation. Common reasons include text in a numeric column, a different date format, or a source value that does not match the step’s expected type. Inspect the value before the step that introduces the error.
Does Column quality check every row? Not necessarily. Power Query’s profiling may use the top 1,000 rows by default. Check the profiling scope control at the bottom of the editor and choose the entire data set when you need a full-data profile; for a large source, allow time for the extra processing.
Should I replace Power Query errors with zero? Only if zero is the correct meaning of the failed value. Zero, null, and an excluded row represent different things. Replacing unknown or invalid amounts with zero can distort totals, so correct the source or use a clearly documented missing-value rule when appropriate.
Can Excel for the web or Mac use the same steps? These directions describe Excel for Microsoft 365 on Windows. Power Query availability, supported sources, and editor controls can differ in Excel for Mac, and Excel for the web does not provide an identical authoring experience. If a named command is missing, check the editor and feature support for your Excel version.
Related
- Combine Two Cells in Excel and Keep Both Text
- Combine in Excel: Text, Cells, and Data
- Company Contact List Template for Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Text to Columns With Dates: Fix Date FormatsTo convert text dates in Excel, select the cells, choose Data > Text to Columns, move through the…
- Excel Formula Not Working? Fix Results Without ErrorsIf an Excel formula gives a blank, old, or incorrect result without showing an error, check calculation settings,…
- Excel Keeps Not Responding? Causes and Fixes to TryIf Excel keeps not responding, first give it a minute if it may be calculating or loading data;…
- How to Fix a SUM Error in ExcelTo fix a SUM error in Excel, first check that the formula points to the right cells, then…
- Excel #REF! Error: Causes and FixesAn Excel #REF! error means a formula contains a reference to a cell, range, or worksheet that Excel…
- Excel Goal Seek Not Working? Fix These Common ProblemsIf Excel Goal Seek is not working, first confirm that the Set cell contains a formula linked to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.