Power Query Text.StartsWith() range of values

[KaiRado]

Hi, 

I would like to filter my data in the TableName table by the following condition: select data where column Code starts with V[2-9] OR M[2-9] OR contains ("MS" OR "PO"). That means that I want data where Code contains "MS" or "PO" or starts with: V2, V3, V4, M2, M3, M4, M5, and so on. 

I wrote the following:

Table.SelectRows(TableName,
each List.Contains({"MS", "PO"}, [Code]) or Text.StartsWith([Code]),....)

)

But I don't know how to finish it, so I get the result I want.

Do you gyus have any ideas?

Thank you very much in advance!

[tackytechtom]

Hi @KaiRado ,

Before:

After:

The code:

= Table.SelectRows ( Tablename, each 
    ( Text.Contains ( [Code], "MS" ) ) or 
    ( Text.Contains ( [Code], "PO" ) ) or 
    ( ( Text.StartsWith ( [Code], "V" ) or Text.StartsWith ( [Code], "M" ) ) and Number.FromText ( Text.At ( [Code] , 1 ) ) >= 2 ) 
) 

Let me know if this fixes your issue 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[KaiRado]

Hi @tackytechtom !

Thank you very much for your solution. It partly worked. The thing is that I'm receiving an error: "We couldn't convert to Number...". I think the problem is that some values of the Code column on the second position contain letters or spaces (for example "MY" or "M J"). 

Thank you for your time!

[tackytechtom · Accepted solution]

Hi @KaiRado ,

Not super nice, but this one maybe?

= Table.SelectRows ( Source, each
    ( Text.Contains ( [Code], "MS" ) ) or 
    ( Text.Contains ( [Code], "PO" ) ) or 
    ( ( Text.StartsWith ( [Code], "V" ) or Text.StartsWith ( [Code], "M" ) ) 
    and List.Contains( {"2","3","4","5","6","7","8","9"},Text.At([Code] , 1 ) ) )
)

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[KaiRado]

Hi @tackytechtom ,

that worked! Thank you very much!