Need help filtering a text column

[Samuelroark01]

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.

[jgeddes · Accepted solution]

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 )

[v-xinruzhu-msft · Accepted solution]

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 · Accepted solution]

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

[v-xinruzhu-msft · Accepted solution]

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.

[jgeddes]

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

[jgeddes · Accepted solution]

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 )

[Samuelroark01]

It appears that the query does not like PREVIOUSSTEP