Forum Discussion
Power Query Text.StartsWith() range of values
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 wan...
Hi Anonymous ,
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/
Hi Anonymous ,
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/
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!
Hi Anonymous ,
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/