Text Filters in Power Query Editor remove null values because the interface defaults to excluding blanks and empty entries from boolean-based filters. This behavior is intentional, as nulls are treated as missing data rather than explicit text strings like "0" or "". To retain these values during filtering operations, you must explicitly check the "Include nulls" option within the filter menu before applying your criteria.
The Default Behavior of Text Filters
When you open the filter dropdown for a column containing text data in the Power Query Editor, you will notice that the list of items to select includes checkboxes next to each unique value. By default, the checkbox labeled "Include nulls" is often unchecked, or the system automatically excludes nulls when you apply specific text filters. This can be confusing for users who expect all rows to remain visible unless they are explicitly filtered out by name. The reason for this design lies in how Power Query handles data types and missing information. In database management and Excel integration, a null value represents an absence of data, distinct from an empty string ("") or zero (0). When you apply a filter such as "Contains 'John'", the engine evaluates every row against this condition. Rows with null values in the target column do not contain the string "John", nor do they contain any string at all. Consequently, they fail the logical test and are excluded from the result set. This exclusion is consistent across most filtering mechanisms in Power Query. Whether you use "Starts With", "Ends With", or "Equals", the evaluation logic treats nulls as non-matching entities. This ensures that your transformations focus on actual data presence, preventing errors that might arise from processing undefined values. However, for many analytical workflows, ignoring nulls entirely leads to incomplete datasets, making it crucial to understand how to manage them manually.Distinguishing Nulls from Empty Strings
It is important to distinguish between a true null value and an empty string. A null indicates that no data exists for that cell, whereas an empty string is a text value of length zero. Power Query treats these differently in many functions. For instance, theText.Length function returns 1 for an empty string but throws an error or behaves unexpectedly if applied directly to a null without handling. When filtering, both may appear visually similar in some views, but only nulls trigger the specific "Include nulls" toggle behavior in the UI. If you need to treat empty strings as valid data points, you should first replace them with a placeholder or convert them using Table.ReplaceValue before applying complex filters.
How to Retain Null Values During Filtering
To ensure that null values are not inadvertently removed when you apply text filters, you must interact with the filter dialog carefully. Follow these steps to preserve your complete dataset:- Open the Filter Menu: Click the small arrow icon located in the header of the column you wish to filter. This opens the standard filter pane.
- Select Your Criteria: Check the boxes for the specific text values you want to keep, such as "Active" or "Pending". You can also use search or advanced filtering options here.
- Enable Null Inclusion: Before clicking OK, locate the checkbox labeled "Include nulls" at the bottom of the list. Ensure this box is checked. If it is unchecked, null values will be excluded from the final query output.
- Apply the Filter: Click the OK button. The query will now process only the selected text values along with any rows where the column contains null data.
Table.SelectRows function in the Advanced Editor to programmatically control which rows are kept, offering greater flexibility than the GUI filter.
Impact on Data Manipulation and Downstream Steps
Excluding nulls prematurely can have significant downstream effects on your data model. Many DAX measures and Excel formulas rely on accurate counts of records. If nulls are filtered out early, your sum, average, or count calculations may be skewed because the denominator changes. For example, calculating the average sales per region might exclude regions with no recorded sales (nulls), artificially inflating the average. Furthermore, joining tables later in the process can become problematic. If one table has been filtered to exclude nulls while another retains them, join operations may yield unexpected results or lose data integrity. It is generally best practice to keep nulls intact until the final stage of your ETL (Extract, Transform, Load) process, unless you have a specific reason to discard missing data. This approach maintains the fidelity of your source data and allows for more transparent auditing of transformations. For related insights on managing data structures and formulas in Excel, you might find Using drop down lists, count formula, and conditional formatting in Microsoft Excel helpful for understanding how to visualize and analyze data once it has been properly cleaned. Additionally, resolving common formula issues like the INDEX-MATCH SPILL ERROR: How to Resolve and Prevent can complement your Power Query skills by ensuring accurate data retrieval after transformation.Advanced Techniques for Null Handling
Beyond simple filtering, Power Query offers robust tools for managing nulls. TheFill Down and Fill Up commands are particularly useful for propagating non-null values into adjacent null cells, effectively cleaning up hierarchical data. You can also use Replace Errors or Replace Value to standardize how missing data appears.
In the Advanced Editor, you can write custom M code to filter rows based on complex conditions involving nulls. For instance, using each [Column] <> null will explicitly exclude nulls, while each [Column] = null or [Column] = "Yes" will include them alongside specific values. Mastering these techniques provides finer control over your data quality and ensures that your reports reflect the true state of your information.
Frequently Asked Questions
Why does my Power Query show blank cells instead of null?
Power Query typically displays nulls as blank cells in the preview pane for readability. However, they are still treated as null data types internally. You can verify this by checking the data type icon in the column header; a null column will usually show a generic icon until you specify a type, whereas a text column will show "ABC".
Can I filter for both nulls and empty strings simultaneously?
No, the "Include nulls" checkbox specifically targets null values. Empty strings ("") are considered valid text content and are included by default if they match your other criteria. To filter for both, you would need to replace empty strings with nulls first using Table.ReplaceValue, then apply the filter.
Does filtering out nulls affect the performance of my query?
Filtering out nulls can slightly improve performance by reducing the number of rows processed in subsequent steps. However, the impact is usually negligible unless you are working with millions of rows. The primary consideration should always be data accuracy rather than minor speed gains.
How do I see the raw M code generated by the filter?
You can view the underlying M code by clicking the "Advanced Editor" button in the Home tab. Look for the Table.SelectRows step, which contains the logic for your filter. This allows you to modify the code manually to include or exclude nulls with precise syntax.
What happens if I accidentally exclude nulls and need them back?
If you have already applied the filter, you can simply go back to the "Applied Steps" pane on the right side of the Power Query Editor. Delete the step named "Filtered Rows" or click on it to edit the parameters. Re-check the "Include nulls" box and click OK to restore those rows.