Forum Discussion

Samuelroark01's avatar
Samuelroark01
New Member
2 years ago
Solved

Need help filtering a text column

Hi There,

 

I have a column called "Container_number". I need to filter the column to only show me:

 

1. Rows that have any non-numeric characters in the text

2. If any rows have more or less that 8 characters in the text

 

Note: The filter should not be sequential. Both filters should be against the original set of data.

 

 

 

 

Need Help
Tutorial Requests
  • jgeddes's avatar
    jgeddes
    2 years ago

    You can add the following line to your query...

     

     

     

     

    = Table.SelectRows(PREVIOUSSTEP, each Text.PositionOfAny([container_number], {"A".."Z"}) <> -1 or Text.PositionOfAny([container_number], {"a".."z"}) <> -1 or Text.Length([container_number]) <> 8 )

     

     

     

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the solution jgeddes  provided, and i want to offer some more information for user to refer to.

    hello Samuelroark01 , you can add a new step and input the following code.

     =Table.SelectRows(your last step name , each Text.Select([container_number],{"A".."Z","a".."z"})<>"" or  Text.Length([container_number])<>8)
//the last step name in your step, e.g #"Changed Type"

    And you can refer to the following m code in advanved editor 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcexDcAwCATAXahT8HwAp0yyRCTE/mtEsuXurkqAsAyVPkpIBVJ5zd0wPvDl9/OYggdoi0bfOD1ySPcP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [container_number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"container_number", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Select([container_number],{"A".."Z","a".."z"})<>"" or  Text.Length([container_number])<>8)
in
    #"Filtered Rows"

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi Samuelroark01, different approach:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Nc27EQAhCEXRXogNeIB8wq3Dsf82VneVgODMZRiD0JUaAS7hTLNtsSWqDARrHeu74ohM+CH/CZZ1L2ORQNLZ41B+FcPgax+sD7OgoT+t5426ZUBuVnt2KGr9iaQ5Xw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, container_number = _t]),
    FilteredRows = Table.SelectRows(Source, each 
        [ a = Text.Length([container_number]),
          b = Text.Length(Text.Select([container_number], {"0".."9"})),
          c = (a <> b) or (a <> 8)
        ][c])
in
    FilteredRows

5 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    2 years ago

    You can add the following line to your query...

     

     

     

     

    = Table.SelectRows(PREVIOUSSTEP, each Text.PositionOfAny([container_number], {"A".."Z"}) <> -1 or Text.PositionOfAny([container_number], {"a".."z"}) <> -1 or Text.Length([container_number]) <> 8 )

     

     

     

     

  • jgeddes's avatar
    jgeddes
    Super User
    2 years ago

    Yep. Replace that with the name of the previous step in your query as it appears in the advanced editor.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

    Thanks for the solution jgeddes  provided, and i want to offer some more information for user to refer to.

    hello Samuelroark01 , you can add a new step and input the following code.

     =Table.SelectRows(your last step name , each Text.Select([container_number],{"A".."Z","a".."z"})<>"" or  Text.Length([container_number])<>8)
//the last step name in your step, e.g #"Changed Type"

    And you can refer to the following m code in advanved editor 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcexDcAwCATAXahT8HwAp0yyRCTE/mtEsuXurkqAsAyVPkpIBVJ5zd0wPvDl9/OYggdoi0bfOD1ySPcP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [container_number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"container_number", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Select([container_number],{"A".."Z","a".."z"})<>"" or  Text.Length([container_number])<>8)
in
    #"Filtered Rows"

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Samuelroark01, different approach:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Nc27EQAhCEXRXogNeIB8wq3Dsf82VneVgODMZRiD0JUaAS7hTLNtsSWqDARrHeu74ohM+CH/CZZ1L2ORQNLZ41B+FcPgax+sD7OgoT+t5426ZUBuVnt2KGr9iaQ5Xw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, container_number = _t]),
    FilteredRows = Table.SelectRows(Source, each 
        [ a = Text.Length([container_number]),
          b = Text.Length(Text.Select([container_number], {"0".."9"})),
          c = (a <> b) or (a <> 8)
        ][c])
in
    FilteredRows