M query removes blank even though not stated

[saud968]

When I add this step - Table.SelectRows(#"Added Custom1", each not Text.Contains([BUSINESS_UNIT], "Help Desk Services")) filters out the data where Business_Unit is blank. Basically this has never happened before where null values were removed. So I am trying to understand if this is proper that it will remove the nulls/blank or is it something else. I did not expect the nulls to be removed

@AlienSx @amitchandak , @lbendlin @dufoq3 

[Nasif_Azam · Accepted solution]

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

[saud968 · Accepted solution]

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"))

[v-pnaroju-msft · Accepted solution]

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.

[dufoq3]

Hi @saud968, try this approach:

?? "" is replacing null values with ""

Before

After

let
    Source = Table.FromColumns({{"A", null, "B", "Be aware", "Benchmark", "Word"}}),
    Filtered = Table.SelectRows(Source, each not Text.Contains([Column1] ?? "", "B"))
in
    Filtered

[saud968 · Accepted solution]

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"))

[Nasif_Azam · Accepted solution]

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

[speedramps]

Crete some test data

If you want to exclude Business Unit "H"  
and to to exclude blank Business Unit 
then try this

= Table.SelectRows(Source, each ([Business unit] <> "" and [Business unit] <> "H"))

If you are a novice then use the Filter wizards.
They are a good way to auto generate, check and learn M code

I want to help you more but your description is too vague. Please write it again.

Dont post buggy code and explain how it did not work, and then forget to tell us what you wanted. That is crazy 😀😀😀

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). ‌‌
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[saud968]

Hi Speed, I apologize, I typed it incompletely. I think this happened because I posted this while I was working, but I want to understand if this is normal behavior to remove null/blank, as it has never happened with me earlier. And wanted to keep the blanks

[MCG]

try

each not Text.Contains([BUSINESS_UNIT], "Help Desk Services") or [BUSINESS_UNIT] = null)

MCG