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 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!

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/

     

4 Replies

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    3 years ago

    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/

    • 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/