FILTER NOT TEXT.STARTSWITH

[GeekAlfPro]

Hello,

i have a strange way of working in filter step.

i have rows within there is the remorque word.

i want to keep the rows that not start with "remorque"

Table.SelectRows(#"Récupération Grp", each not Text.StartsWith([Genre Machine], "REMORQUE"))

so far so good, but the filter removes also null rows 

is it normal ?

thanks 

[AlexisOlson]

Nulls have special behavior.

Text.StartsWith(null, "REMORQUE" ) = null

thus

not Text.StartsWith(null, "REMORQUE" ) = not null = null

 Since Table.SelectRows only chooses rows where the condition evaluates to true, you lose the null rows.

To fix this, here are a couple of options:

1. Handle null explicitly.

each [Genre Machine] = null or not Text.StartsWith([Genre Machine], "REMORQUE"))

2. Use the coalesce operator to replace null with something else like the emty string.

each not Text.StartsWith([Genre Machine] ?? "", "REMORQUE")

[GeekAlfPro]

Thank you @dharmendars007 that was it !

[dharmendars007 · Accepted solution]

Hello @GeekAlfPro , 

Please use the below modified code to retain the null values..

Table.SelectRows(#"Récupération Grp", each [Genre Machine] = null or not Text.StartsWith([Genre Machine], "REMORQUE"))

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

LinkedIN