Delete rows containing string.

[Anonymous]

Hey guys 🙂

I want to remove rows containing string in power query. Is there any option to solve my problem? 🙂

[v-eachen-msft]

Hi @Anonymous ,

You could split the column to digital column and non-digital column. Then keep the "null" rows in the non-digital column. At last, remove the extra column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VIrVAdGOYNrEyEQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1.1", "Column1.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Character Transition", each ([Column1.2] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1.2"})
in
    #"Removed Columns"

[Mariusz · Accepted solution]

Hi @Anonymous 

If you are looking to preserve rows that contain numbers only, you can try this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKNjQyVorViVaC0cYQKrEixdTMXCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Length( Text.Remove([Column1], {"0".."9"} ) ) = 0 )
in
    #"Filtered Rows"

Also, see the attached for the ref.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

@Mariusz  i mean rows e.g. "1234A235" and so on... 🙂

[Mariusz]

Hi @Anonymous 

Sure, try the solution in my previous post it do just that.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

considers that:

Text.Length( Text.Remove("+2345645", {"0".."9"} ))=1

try

Value.Type(Value.FromText("+2345645"))

or

Number.FromText("+2345645")

Number.FromText("+234wa5645")

or

Value.Is(value as any, type as type) as logical

PS

Could please someone explain me how to do to attach/upload some scriptfile?

[Mariusz]

Hi @Anonymous 

Sure, can you provide a data sample for your scenario?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn