Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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...
Need Help
  • tackytechtom's avatar
    tackytechtom
    3 years ago

    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/

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
tackytechtom
Most Valuable Professional
3 years ago

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/

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi tackytechtom ,

     

    that worked! Thank you very much!