M query removes blank even though not stated

Hey saud968 ,

This behavior you're encountering in Power Query (M language) is actually expected due to how Text.Contains() interacts with null values. Here's a full explanation:

Why Blank (Null) Rows Are Being Removed

In your step:

Table.SelectRows(#"Added Custom1", each not Text.Contains([BUSINESS_UNIT], "Help Desk Services"))

If [BUSINESS_UNIT] is null, then Text.Contains(null, "Help Desk Services") will return null, and not null is also null.

Since Table.SelectRows only retains rows where the condition is true, nulls and false are both excluded. That's why rows with blank (null) BUSINESS_UNIT values are getting filtered out even though you're not explicitly filtering them.

How to Fix It: Make It Null-Safe

Wrap your condition in a Record.FieldValues check to exclude only actual matches and keep nulls:

Table.SelectRows(#"Added Custom1", each [BUSINESS_UNIT] = null or not Text.Contains([BUSINESS_UNIT], "Help Desk Services"))

This keeps:

• rows where BUSINESS_UNIT is null

• rows where it does not contain "Help Desk Services"

Alternative: Using Text.Contains Safely

If you prefer a defensive style:

Table.SelectRows(#"Added Custom1", each not (Text.Contains(Record.Field(_, "BUSINESS_UNIT")?, "Help Desk Services") ?? false))

The ?? false ensures that if the value is null, it treats it as false meaning the row won't be excluded.

Summary

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

I have fixed the null issue, but I wanted to understand if this is a normal behavior of this m query = Table.SelectRows(#"Added Custom1", each not Text.Contains([BUSINESS_UNIT], "Help Desk Services"))

Thankyou, MCG, speedramps, Nasif_Azam and dufoq3 for your responses.

Hi saud968,

Thank you for your follow-up. We are pleased to learn that you have resolved the null issue.

From my understanding, the behavior you are observing is expected in Power Query. When using the expression:
Text.Contains([BUSINESS_UNIT], "Help Desk Services")
if [BUSINESS_UNIT] is null, the function returns null (and not false). Since Power Query retains only those rows where the condition evaluates explicitly to true, the null values are excluded, even if they are not directly filtered out.

This is not a change in behavior but rather a reflection of how null logic operates in M language. Utilizing ?? "" or adding an explicit null check ensures that those rows are retained.

Additionally, please find below the relevant links for your reference:
Text.Contains - PowerQuery M | Microsoft Learn
Operators - PowerQuery M | Microsoft Learn

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will help other community members who encounter similar queries.

Should you have any further questions, please feel free to reach out to the Microsoft Fabric community.

Thank you.