Forum Discussion
table selectrows looking for more than one thing
Can anyone help me figure out where I have gone wrong? I am trying to grab more rows based on the common variations of "no note from tutor" but adding an "and" breaks the current working function
This one works
= Table.SelectRows(#"Exclude Blank and NA Notes", each Text.Contains([Notes], "No note from Tutor"))
This one is broken
= Table.SelectRows(#"Exclude Blank and NA Notes", each Text.Contains([Notes], "No note from Tutor" and "No notes from Tutor"))
You need to repeat the entire test (Text.Contains)
and you need to connect the tests with an or , not an and
I also suggest you make the test case-insensitive
= Table.SelectRows(#"Exclude Blank and NA Notes", each Text.Contains([Notes], "No note from tutor", Comparer.OrdinalIgnoreCase) or Text.Contains([Notes], "No notes from tutor",Comparer.OrdinalIgnoreCase))You could also use the List.Contains method as suggested by jgeddes but, again, I would add the case insensitive option:
= Table.SelectRows(#"Exclude Blank and NA Notes", each List.Contains({"No note from tutor","No notes from tutor"}, [Notes], Comparer.OrdinalIgnoreCase))
3 Replies
When you use Text.Contains you need to restate the column reference. This should work...
Table.SelectRows(#"Exclude Blank and NA Notes", each Text.Contains([Notes], "No note from Tutor") and Text.Contains( [Notes], "No notes from Tutor"))You may also want to consider using List.Contains.
Table.SelectRows(#"Exclude Blank and NA Notes", each List.Contains({"No note from Tutor", "No notes from Tutor"}, [Notes]))You need to repeat the entire test (Text.Contains)
and you need to connect the tests with an or , not an and
I also suggest you make the test case-insensitive
= Table.SelectRows(#"Exclude Blank and NA Notes", each Text.Contains([Notes], "No note from tutor", Comparer.OrdinalIgnoreCase) or Text.Contains([Notes], "No notes from tutor",Comparer.OrdinalIgnoreCase))You could also use the List.Contains method as suggested by jgeddes but, again, I would add the case insensitive option:
= Table.SelectRows(#"Exclude Blank and NA Notes", each List.Contains({"No note from tutor","No notes from tutor"}, [Notes], Comparer.OrdinalIgnoreCase))Hi Nephandi99 ,
If ronrsnfld , jgeddes response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found that response helpful.
Thank you
