Search code examples
powerquery

How to remove an entire row when any of the cells contains a specific value or string?

I have a table in Power Query that loads with an unknown number of columns and rows. In all this data, there is a specific cell that will contain the text "item sum". I want to know how to remove the entire row of data that contains the text "item sum"

Hence in below example, row 133 would be removed from the table

enter image description here

Solution

  • Easiest is to add a column that checks for the text, then filter on that column

    add column .. custom column ... with

    = List.Sum(List.Transform(Record.FieldValues(_), each try if Text.Contains(_,"Item sum") then 1 else 0 otherwise 0))

    then use arrow atop that new column to uncheck the 1

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each List.Sum(List.Transform(Record.FieldValues(_), each try if Text.Contains(_,"Item sum") then 1 else 0 otherwise 0))),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] <>1)
in  #"Filtered Rows"