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


     

v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity 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"