Power Query Editor: Why do Text Filters Remove null Values?

Power Query Editor is a powerful tool that allows users to transform and clean data within Microsoft Excel. One common question that arises when using the Text Filters feature in Power Query Editor is why it removes null values. In this article, we will explore why null values are removed and how to work around this limitation.

When working with data in Power Query Editor, null values represent missing or unknown data. These null values can occur for various reasons, such as incomplete data entry or data that is not available. In some cases, null values may be intentionally left blank to indicate a lack of information.

By default, when applying a Text Filter in Power Query Editor, the filter removes any rows that contain null values. This behavior is designed to help users filter out incomplete or irrelevant data from their datasets. However, this can also lead to unexpected results if null values are important for analysis or reporting.

To understand why null values are removed by default, it's important to consider the purpose of Text Filters. Text Filters are primarily used to filter data based on specific criteria, such as text matching or pattern matching. In most cases, null values do not meet these criteria and are therefore excluded from the filtered results.

While the default behavior of removing null values can be useful in many scenarios, there are cases where you may want to include null values in your filtered results. To achieve this, you can use a workaround by modifying the Text Filter criteria.

Instead of selecting a specific text or pattern to filter, you can use the "Does Not Equal" operator and leave the filter criteria blank. This effectively tells Power Query Editor to include all rows, including those with null values, in the filtered results. Here's how you can do it:

1. Select the column you want to filter in Power Query Editor
2. Go to the "Text Filters" dropdown menu
3. Select "Does Not Equal"
4. Leave the filter criteria blank
5. Click "OK" to apply the filter

By using the "Does Not Equal" operator with blank criteria, you can include null values in your filtered results. This workaround allows you to retain the null values for further analysis or reporting.

It's important to note that including null values in your filtered results may affect calculations or visualizations that rely on complete data. Make sure to consider the impact of null values on your analysis and adjust accordingly.

Conclusion

Power Query Editor's Text Filters feature is a powerful tool for data transformation and cleaning in Microsoft Excel. By default, the Text Filters remove null values from the filtered results. However, you can use a workaround by using the "Does Not Equal" operator with blank criteria to include null values in your filtered results. Remember to consider the impact of null values on your analysis and adjust accordingly.

References