Forum Discussion
FILTER NOT TEXT.STARTSWITH
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
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
3 Replies
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
Thank you dharmendars007 that was it !
Nulls have special behavior.
Text.StartsWith(null, "REMORQUE" ) = nullthus
not Text.StartsWith(null, "REMORQUE" ) = not null = nullSince 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")
