Removing Entire Group of Records if it Contains a Record With a Value

[Txtcher]

Somehow I know this can't be hard, but ...

I have a table of Applications with event values. Each App can have more than one event value. Hence App ID is not distinct. I want to eliminate the entire group of App ID's if the App has a event value = COMPLETE.

Original Table

APP NO	VALUE
APP-1	IN REVIEW
APP-1	COMPLETE
APP-2	RESPONSE REQUIRED
APP-3	IN REVIEW
APP-3	IN ANALYSIS

DESIRED OUTCOME:  All records for APP-1 are removed because the APP-1 group contained a record with a Value = COMPLETE

APP NO	VALUE
APP-2	RESPONSE REQUIRED
APP-3	IN REVIEW
APP-3	IN ANALYSIS

[Chewdata · Accepted solution]

Hey, 

This can be done by:
1. Group the data on the App, choosing All rows.
2. Add a column that checks if the Value column of nested table contains the value COMPLETED
3. Filter apps that are completed.
4. Expand the table.

let
    Source = YOURDATA,
    GroupRows = Table.Group(Source, {"APP NO"}, {{"Table", each _, type table [APP NO=nullable text, VALUE=nullable text]}}),
    add_Completed = Table.AddColumn(GroupRows, "Completed", each if List.Contains([Table][VALUE], "COMPLETE") then true else false, Logical.Type),
    filter_CompletedTrue = Table.SelectRows(add_Completed, each ([Completed] = false)),
    expand_Table = Table.ExpandTableColumn(filter_CompletedTrue, "Table", {"VALUE"}, {"VALUE"})
in
    expand_Table

Output:

Hopefully this helps!

If my solution helps, please consider to accept it as a solution, so other users can quickly find the answer!

[Txtcher]

Awesome! That worked beautifully. I kinda took this approach originally, but it was clumsy. I first created a table for only those apps with a value (field is called New_Value__c) of COMPLETE. 

let
// Merge Regulatory Event with Payment Received to filter RE's for only those Apps with 120 Day Due Dates in prior month
Source = Table.NestedJoin(#"Regulatory Event", {"App.Id"}, #"Payment Received", {"App.Id"}, "Payment Received", JoinKind.Inner),
Expanded120DayAssignedTo = Table.ExpandTableColumn(Source, "Payment Received", {"120 Days", "Assigned To", "Payment Received"}, {"120 Days", "Assigned To", "Payment Received.1"}),
GroupByNewValue = Table.Group(Expanded120DayAssignedTo, {"New_Value__c"}, {{"Count", each _, type table [New_Value__c=nullable text, RE.Id=text, Effective_Date__c=date, Type__c=text, App.Id=text, App.Name=text, App.Form_Name__c=text, App.Form_Type__c=text, App.Program_Type__c=text, 120 Days=date, Payment Received.1=date]}}),
FilterCompleteValue = Table.SelectRows(GroupByNewValue, each ([New_Value__c] = "COMPLETE")),
Expand = Table.ExpandTableColumn(FilterCompleteValue, "Count", {"RE.Id", "Effective_Date__c", "Type__c", "App.Id", "App.Name", "App.Form_Name__c", "App.Form_Type__c", "App.Program_Type__c", "120 Days", "Payment Received.1"}, {"RE.Id", "Effective_Date__c", "Type__c", "App.Id", "App.Name", "App.Form_Name__c", "App.Form_Type__c", "App.Program_Type__c", "120 Days", "Payment Received.1"})
in
Expand

Then I created a second table with the same Source, and filtered it using the table above:

let
// Merge Regulatory Event with Payment Received to filter RE's for only those Apps with 120 Day Due Dates in prior month
Source = Table.NestedJoin(#"Regulatory Event", {"App.Id"}, #"Payment Received", {"App.Id"}, "Payment Received", JoinKind.Inner),
Expanded120Day = Table.ExpandTableColumn(Source, "Payment Received", {"120 Days", "Assigned To"}, {"120 Days", "Assigned To"}),
// Merge all Apps with Apps with RE Complete using Left Anti join to remove all Apps that have an RE Complete Entry
MergeAppsWithReCompleteLeftAnti = Table.NestedJoin(Expanded120Day, {"App.Id"}, #"Apps with RE Complete", {"App.Id"}, "Apps with RE Complete", JoinKind.LeftAnti),
RemoveMergeColumn = Table.RemoveColumns(MergeAppsWithReCompleteLeftAnti,{"Apps with RE Complete"}),
// Group by App and max Effective Date to get current app status
GroupByAppMaxEffectDate = Table.Group(RemoveMergeColumn, {"App.Name"}, {{"Count", each Table.Max(_,"Effective_Date__c")}}),
ExpandGroup = Table.ExpandRecordColumn(GroupByAppMaxEffectDate, "Count", {"New_Value__c", "RE.Id", "Effective_Date__c", "Type__c", "App.Assigned_to__c", "App.Form_Name__c", "App.Form_Type__c", "App.Id", "App.Program_Type__c", "120 Days", "Assigned To"}, {"New_Value__c", "RE.Id", "Effective_Date__c", "Type__c", "App.Assigned_to__c", "App.Form_Name__c", "App.Form_Type__c", "App.Id", "App.Program_Type__c", "120 Days", "Assigned To"})
in
ExpandGroup

 Your solution is much cleaner. Thank you!

[jgeddes]

You might be able to use something like this...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgwI0DVU0lHy9FMIcg3zdA1XitVBiDr7+wb4uIa4wgWNgIJBrsEB/n7BrkANgaGeQa4ucFljrAZBRR39HH0igz2DlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"APP NO" = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"APP NO", type text}, {"VALUE", type text}}),
    completedApps = List.RemoveNulls(Table.AddColumn(#"Changed Type", "completeApps", each if [VALUE] = "COMPLETE" then [APP NO] else null, type text)[completeApps]),
    filterTable = Table.SelectRows(#"Changed Type", each not List.Contains(completedApps, [APP NO]))
in
    filterTable

The idea is to extract a list of 'APP NO' that are complete and then use that list to filter the table.

Hope this helps.