Forum Discussion

andrew wilson's avatar
andrew wilson
Copper Contributor
Feb 28, 2018

filtering/sorting a deleting

Hi guys

I have a spreadsheet I'm trying to sort, I have a list of numbers (including duplicates) which either come back with a pass or fail on different dates.

If the newest date is a "fail" I want to keep that and delete the previous fails, And if it's a pass I want to delete the pass and all the previous fails relating to the specific number.

excel
  • Willy Lau's avatar
    Willy Lau
    Steel Contributor
    Mar 01, 2018

    Can I say in this way:

    Keep the latest record of each SN if it is fail. Otherwise, delete all records about that SN.

     

    If not, could you please provide a sample file about what you want to accomplish?  Thank you.

    • andrew wilson's avatar
      andrew wilson
      Copper Contributor
      Mar 01, 2018

      Hi Willy

       

      Thanks for the reply I have added Sample with text.

       

      Im sure it's a simple fix but, but not to hot on the old "marcos" if that's even what would be required to solve my issue.

       

      Cheers

      Andy 

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Mar 02, 2018

        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.

         

Resources