filtering/sorting a deleting

Here is the variant with Power Query (aka Get&Transform). If I understood the logic correctly we remove all records for IDs who passed, and keep only latest FAILED record for ones who didn't pass.

The script could be like

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
    // Select only IDs who PASSED
    OnlyPassed = Table.SelectRows(Source, each ([Inspection Outcome] = "PASSED")),
    PassedID = Table.SelectColumns(OnlyPassed,{"Number"}),
    
    // Merge with source table keeping only IDs who FAILED
    SelectFailed= Table.NestedJoin(PassedID,{"Number"},Source,{"Number"},"Source",JoinKind.RightAnti),
    KeepTableOnly = Table.SelectColumns(SelectFailed,{"Source"}),
    ExpandTable = Table.ExpandTableColumn(KeepTableOnly, "Source",
      {"Works Recorded Date", "Number", "End", "Insp. Date/Time", "Inspection Outcome"},
      {"Works Recorded Date", "Number", "End", "Insp. Date/Time", "Inspection Outcome"}),
    
    // Now remove duplicated IDs keeping latest Inspection Date/Time
    SortForRemoving = Table.Sort(ExpandTable,{{"Number", Order.Ascending}, {"Insp. Date/Time", Order.Descending}}),
    Buffer = Table.Buffer(SortForRemoving), // need the buffer for proper duplicates removing
    RemoveDuplicatedID = Table.Distinct(Buffer, {"Number"}), // keep only latest FAILED
    
    // Change on proper data types before return, mainly for Date and Date/Time columns
    ProperType = Table.TransformColumnTypes(RemoveDuplicatedID,{
      {"Works Recorded Date", type date},
      {"End", type date},
      {"Insp. Date/Time", type datetime},
      {"Number", Int64.Type},
      {"Inspection Outcome", type text}
   })
in
    ProperType

Exactly the same could be done from user interface only if use separate queries for IDs who passed and source one.

Result is in second sheet of the attached file.