Power Query Data Validation in Excel: What It Can Do

How-to basics · Updated November 18, 2026

Power Query does not apply Excel’s interactive Data Validation rules to cells or stop someone from entering invalid data. It can, however, check imported or combined data during a query, flag or remove records that fail your rules, and load the results back to Excel; use Data > Data Validation when you need to restrict what people can type into worksheet cells.

What data validation means in Power Query

People use “data validation” to describe two related but different jobs. In a worksheet, Excel Data Validation controls input: it can offer a dropdown, set a numeric or date limit, or show an error when a user enters something unacceptable. Power Query works on data as it is brought in, reshaped, and refreshed. It can inspect values and apply your quality rules to the data being processed, but it is not an input-control feature.

This distinction matters because a Power Query result is usually a refreshed output, not a live form. A query can identify a blank customer ID in a source file and mark that row as an exception. It cannot prevent someone from typing a blank ID into the original source system, nor does it automatically create an error alert on the query output cells.

Power Query is most useful for repeatable checks on data from sources such as workbooks, text files, databases, or folders. Once you define steps, a refresh reapplies them to new source data. That makes it a good fit for cleanup and quality checks that would otherwise be repeated manually.

How Power Query checks data

A query is a sequence of transformation steps. Some steps infer or assign data types, such as text, whole number, decimal number, or date. Others filter rows, split columns, replace values, group records, or add a calculated column. You can combine these steps into a validation workflow: standardize the data, test it against business rules, and separate acceptable rows from exceptions.

In Excel for Microsoft 365 on Windows, select a cell in an Excel table and choose Data > From Table/Range to open it in the Power Query Editor. For a file or other source, use Data > Get Data and select the relevant source. In the editor, you can inspect data types and use View > Column quality, View > Column distribution, and View > Column profile to find empty, valid, or error values and understand what a column contains. Profiling is diagnostic: it helps reveal issues but does not itself enforce a rule.

For a rule that needs a clear outcome, add a custom column. In the editor, choose Add Column > Custom Column, enter a name such as Validation status, and use an M expression. For example, if the source has a Quantity column, this expression labels missing or nonpositive quantities:

if [Quantity] = null then "Missing" else if [Quantity] <= 0 then "Invalid quantity" else null

The expression returns Missing when Quantity is null, Invalid quantity when it is zero or below, and null otherwise. The source column should have a suitable numeric type; if it contains text that cannot be converted, a type-conversion error may occur before this check. You can then filter Validation status to show only exceptions, or retain the status column in the output for review.

A practical validation workflow

A reliable workflow makes the checks visible and repeatable rather than silently discarding questionable records. One approach is:

  1. Keep the source intact. Connect to the original source and perform cleanup in the query, rather than overwriting the source data.
  2. Set or confirm data types. Treat IDs and postal codes as text when leading zeros or letters are meaningful; use numeric types for quantities and date types for dates.
  3. Normalize comparable values. For example, trim extra spaces in category names and standardize capitalization before comparing them with an allowed list.
  4. Add rule checks. Create status columns for required fields, allowed categories, reasonable ranges, or relationships between fields.
  5. Review exceptions. Filter to failed records and inspect why they failed. Keep an exception output if another person needs to correct the source.
  6. Load and refresh. Choose Home > Close & Load to return results to Excel. Later, use Data > Refresh All to rerun the query against its source.

For example, an order import might require an Order ID, a date, and a positive quantity. A row with a blank ID can receive a Missing ID status; a row with a text value in a numeric quantity field may produce a conversion error; and a row with quantity -3 can receive an Invalid quantity status. Keeping these outcomes separate helps distinguish an absent value from a value that is present but unacceptable.

Common rules and useful examples

Power Query can support many checks, but the rule should match the data and the people responsible for fixing it. Common checks include:

  • Required fields: flag null or empty values in customer IDs, dates, or other required columns.
  • Allowed values: compare a status or region against a maintained reference table, rather than relying on inconsistent spelling in source files.
  • Ranges: identify negative quantities, dates outside a reporting period, or values beyond a defined threshold.
  • Duplicates: group by a key such as Invoice ID and inspect groups with more than one record. A duplicate is not always an error, so confirm the business rule before removing rows.
  • Cross-field consistency: check whether a delivery date precedes an order date, or whether a line total is consistent with the fields used to calculate it.

For allowed values, a reference query or table is usually easier to maintain than embedding a long list in several steps. You can merge the incoming data with the approved list and identify values with no match. This is especially useful when the list changes, because maintaining one source of approved categories is less error-prone than editing multiple validation rules.

For worksheet entry, the comparable Excel feature is different. If cells B2:B100 should contain positive numbers, select B2:B100, choose Data > Data Validation, set Allow to Custom, and enter this rule, assuming B2 is the active cell in the selection: =AND(ISNUMBER(B2),B2>0). A value of 12 returns TRUE and is allowed; 0 and text return FALSE and are rejected if the error alert is enabled. This rule acts on entries in those cells; it is not a Power Query expression.

Handling errors without hiding them

A common mistake is to remove every row that causes an error and assume the remaining table is correct. An error can indicate a genuine bad value, but it can also reveal a source-format change, an unexpected blank, or an overly strict type conversion. For example, converting a column to whole numbers will fail for a text entry such as N/A. Decide whether N/A should be treated as missing, flagged for correction, or represented another way before removing the row.

Power Query’s error-handling options can help isolate problem rows. In the editor, select a column and use the Home tab’s error-related row options to keep or remove rows with errors, where available in the selected context. Another approach is to add an explicit status column before filtering, so the reason for a row’s failure remains understandable. If you replace errors with null, document that choice: a blank may otherwise look like a legitimate missing value rather than a conversion problem.

Column profiling also has limits. In many Power Query Editor configurations, profiling initially uses the top 1,000 rows; check the profiling indicator at the bottom of the editor and switch to the entire data set when appropriate. A clean sample does not prove that later rows are clean.

Power Query versus Excel Data Validation

Choose based on where the problem needs to be addressed. Excel Data Validation is for controlling worksheet input. It can create an in-cell dropdown from a list and show an input message or error alert. It is appropriate for a small entry form or a manually maintained worksheet. It does not replace a full audit of external data, and pasted or imported values can require separate checks.

Power Query is for repeatable transformation and checking of data that is imported or consolidated. It can inspect many records, add status fields, compare against reference data, and refresh its results. It does not provide a live dropdown in the source system and generally does not write corrections back to the original file or database. If you need both controlled entry and repeatable import checks, use Data Validation at the point of entry and Power Query as a downstream quality check.

Refresh, output, and maintenance

After loading the query, the output table reflects the most recent refresh, not necessarily the source’s current state. Data > Refresh All reruns the workbook’s queries, subject to source availability, credentials, and query settings. A refresh can change the number or order of output rows, so avoid treating manually typed notes beside a query result as permanent record-level annotations. Store corrections in the source or in a separate maintained table and join them into the query when needed.

Good maintenance includes naming steps clearly, keeping exception results available, and documenting what each rule means. If a new source file changes a column name or format, a query step may fail or produce unexpected results. Review the output after changes to the source, and do not assume that a successful refresh means every business rule passed. A query can run successfully while still returning rows marked as invalid.

Frequently Asked Questions

Can Power Query create a dropdown list in Excel?
No. Use Data > Data Validation and choose List to create a worksheet dropdown. Power Query can prepare or clean the list of allowed values, but Data Validation is what applies the dropdown to selected cells.

Can Power Query stop users entering invalid data?
No. Power Query checks data during a query refresh; it does not control what users type into a source file or worksheet. Set up Excel Data Validation for worksheet entry, or use controls in the original application if the source is elsewhere.

Can Power Query find blank cells?
Yes. It can identify null values and, depending on how the source represents them, empty text values. Decide whether spaces count as blank; trimming text before checking can reveal values that appear empty but contain spaces.

Does Power Query automatically fix invalid records?
Only if you define transformation steps that change or remove them. It does not infer the correct business answer for a bad value. Flag uncertain records for review rather than replacing them with a guess.

Will validation run when I open the workbook?
It runs when the query is refreshed, either manually or through configured refresh behavior. Check the query’s refresh settings and confirm that its source is available; a prior successful result may be out of date.

Related

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.