Filtering out previous occurrences BEFORE the date of an event

[slittleson]

Evening.  I have a table of patient data (2 million rows) that lists all the admissions for a group of patients,  At some point in one of their admissions, an event takes place.  On subsequent admissions, this event may or may not happen again.  I am looking to exclude all the admissions BEFORE the FIRST occurence of the event, but leave in all subsequent admissions whether the event happened or not.  Example

Patient	Admission Date	Event Happened	Calculated Outcome
1	1/9/21	No	Remove
1	2/10/21	No	Remove
1	3/10/21	Yes	Include
1	10/10/21	No	Include
1	1/11/21	Yes	Include
1	2/12/21	No	Include
2	2/12/21	No	Remove
3	1/10/21	No	Remove
3	2/11/21	Yes	Include
3	5/12/21	Yes	Include
4	1/10/21	Yes	Include

So I can identify the earliest date the event happened, but I can't work out how to remove admissions before this, and whether a loop needs to be involved.  Grateful for any help

Regards,

Steve

[Greg_Deckler · Accepted solution]

@slittleson Well, you could create a separate query where you first filter for Yes. Then you would group by patient and do an aggregation of Minimum of Admission. You could then merge this back into your other query and expand the minimum date found for each patient. Then you could create a calculated column as to whether the date is less than this minimum date and if so flag it. Then filter out the flags.

let
    Source = Table,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Event Happened] = "Yes")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Patient"}, {{"MinDate", each List.Min([Admission Date]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Patient"}, Table, {"Patient"}, "Table", JoinKind.RightOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Patient", "Admission Date", "Event Happened", "Calculated Outcome"}, {"Patient.1", "Admission Date", "Event Happened", "Calculated Outcome"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Patient"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom", each if [MinDate] = null then "" else if [Admission Date] < [MinDate] then "RemoveRow" else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Custom", "MinDate"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Patient.1", "Patient"}})
in
    #"Renamed Columns"