Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Delete rows containing string.

Hey guys 🙂

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

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    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


     

6 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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

     

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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?

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    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"