Help with filter or if statement

[MitaSaxena]

Excel file for powerbi filter help 

Hi, please can someone help. I have a sequence of communication events associated with a job and the date they are logged. 

I want to be able to only see the latest 'Emailed query' row if not followed by events called 'General Helpdesk Job Note' or 'Response by contractor'

Really appreciate any suggestion for this. We have thousands of rows of events for so many jobs - and want to run a report so that we can filter out jobs where we have had received a response from the contractor so we can chase the ones who have not responded back and I cannot think of a way to filter that out. This powerbi report is linked for sql.

[v-janeyg-msft]

Hi, @MitaSaxena 

Did you refer to @AlexisOlson  answer and suggestion? Can the problem be solved?

I want to ask you why it shows green and not red, I don't see the rule. Can you explain more clearly?

Best Regards,
Community Support Team _ Janey

[AlexisOlson]

You can select the even types in question (Emailed query, General Helpdesk Job Not, Response by contractor) and the group by Job Seq taking the top row ordered by datetime. Then expand this row and filter only the Emailed Query rows.

let
    Source = <Your Excel Table>,
    #"Filtered Rows" = Table.SelectRows(Source, each ([EVENT NAME] = "Emailed Query" or [EVENT NAME] = "General Helpdesk Job Note" or [EVENT NAME] = "Response by contractor")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"JOB ID"}, {{"LastEvent", each Table.Max(_, "DATE & TIME"), type record}}),
    #"Expanded LastEvent" = Table.ExpandRecordColumn(#"Grouped Rows", "LastEvent", {"EVENT ID", "EVENT NAME", "DATE & TIME", "COMMENT"}, {"EVENT ID", "EVENT NAME", "DATE & TIME", "COMMENT"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded LastEvent", each ([EVENT NAME] = "Emailed Query")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"EVENT ID", "EVENT NAME", "DATE & TIME", "COMMENT", "JOB ID"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"EVENT ID", Int64.Type}, {"EVENT NAME", type text}, {"DATE & TIME", type datetime}, {"COMMENT", type text}, {"JOB ID", Int64.Type}})
in
    #"Changed Type1"

The only tricky bit is the Table.Max part in the Group By step. For more detail on that, see Approach #3 from my post here:
Select Distinct Rows Ordered by Another Column

[MitaSaxena]

Thank you, I have added the link to the file - Excel file for powerbi filter help 

[AlexisOlson]

Thanks. That helps with the output side but I didn't find any example input.

[MitaSaxena]

Hi Alexis, thank you for looking at this; the first 5 column A to E are input; and I to L are expected output. Thanks again

[AlexisOlson]

Please share your data in a format that we can at least copy and paste from.

Also, what is the desired result? Do you want to remove just that one row, all the rows for that job, or something else?