Forum Discussion
andrew wilson
Feb 28, 2018Copper Contributor
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 wa...
andrew wilson
Mar 01, 2018Copper Contributor
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
Mar 02, 2018Diamond Contributor
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.