removing multiple rows with same data

[Vidushi_Mangal]

HI all,

I want to delete all the rows which has ".." value in column name "Street_No". Can somebody help me to acheive this?

[dufoq3 · Accepted solution]

What is the difference between filter and delete? Power Query can't change the source data - you can change only the output.

[ann_2024 · Accepted solution]

Hi @Vidushi_Mangal 

You can remove it in the Power Query using an additional step

let
    Source = ...,
    #"Removed Rows" = Table.SelectRows(Source, each [Street_No] <> ".."))
in
    #"Removed Rows"

If this doesnt work for you, you have to remove the columns from the source itself.

Thanks.

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solutions KhaledAbdrhman ,ann_2024,dufoq3  and jennratten offered, and i want to offer some more infotmation for user to refer to.

hello @Vidushi_Mangal , based on your description, you can create a blank query, and input the following code in advanced editor. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQy1gMCpVgdMBtMm5iagenEpGQ9iGQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Street_No = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Street_No", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type",{"Street_No",each if Text.Contains(_,"..") then null else _}),
    #"Removed Blank Rows" = Table.SelectRows(Custom1, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

And you can refer to the attachment.

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.

[KhaledAbdrhman]

Hi, 

Select your column in power query and try to sort it so that the rows that contain the specific word you want is above each other, then select delete rows and delete them now it should be easier, hope this helped you.

[ann_2024 · Accepted solution]

Hi @Vidushi_Mangal 

You can remove it in the Power Query using an additional step

let
    Source = ...,
    #"Removed Rows" = Table.SelectRows(Source, each [Street_No] <> ".."))
in
    #"Removed Rows"

If this doesnt work for you, you have to remove the columns from the source itself.

Thanks.

[jennratten]

Hello, select the column named "Streen_No", choose the filter button on the header.  Follow the prompts to select rows which do not contain "..".

[Vidushi_Mangal]

Thanx for response

But this will only filter the table and will not remove the rows. I want to delete them permanently.

[dufoq3 · Accepted solution]

What is the difference between filter and delete? Power Query can't change the source data - you can change only the output.