Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

Remove lines based on character limit

Hi!

I need to remove lines from my data that has less than 9 characters on it. How can I do it?

Thanks!

Hi Anonymous ,

Please refer to my sample.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSoQDpVgdJC6MB6GhPFQlUHWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Removed length less than 9" = Table.SelectRows(
        #"Changed Type", 
        each not List.IsEmpty(
            List.RemoveMatchingItems(
                {Text.Length([Column1])}, {0..8}
            )
        )
    )
in
    #"Removed length less than 9"

Best regards,
Lionel Chen

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

2 Replies

Greg_Deckler
Community Champion
5 years ago
Anonymous Add a new column in Power Query and use Text.Length([Column]). Then you can filter based on that column and then remove the column.

Text.Length - PowerQuery M | Microsoft Docs

Community Support

Hi Anonymous ,

Please refer to my sample.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSoQDpVgdJC6MB6GhPFQlUHWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Removed length less than 9" = Table.SelectRows(
        #"Changed Type", 
        each not List.IsEmpty(
            List.RemoveMatchingItems(
                {Text.Length([Column1])}, {0..8}
            )
        )
    )
in
    #"Removed length less than 9"

Best regards,
Lionel Chen

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

Related content