Hide / remove rows based on criteria

[SP7273]

Only new to this and have bitten off more that I can chew.
I have a large amount of data and need to clean it up by hiding / removing row that are not relevant to the outcome.
In the screen shot below I need to keep only the first two newest rows by

Employee ID, Standard ID, if both have a Verification_score >= 90% and Batch Type is not Formal-OffJob or Formal-OnJob.

if not I would like to hide / remove the rows.
Can this be done in Power Query?
Thanks in advance to anyone for any information on this.

[edhans]

See if this works. If it doesn't though you must provide usable data. I cannot use screencaps. Lots of typing.

I made this table up.

A screenshot of what the expected results would be though is fine and appreciated.

Based on your critera, I should keep the green rows and not the red ones, nor any others as they are not in the two most recent.

I returned this:

Here is my code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWNjfSNLfSMDI2Mg20DPEkh65qVlpuakKMXqIFQZGukbG4KUGUGU4VJnok+MKqBhRKgy1rcAqTLErgqk2QjD/RY4VBFyP0wdfvfDVOF3P8Jl+NwPcq8x1vB3yy/KTczR9U9L88pPQlGLzRe4VWP6BbdaTB/hVovpLzS1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Standard ID" = _t, Date = _t, Score = _t, #"Batch Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Score", Currency.Type}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Employee ID", Order.Ascending}, {"Standard ID", Order.Ascending}, {"Date", Order.Descending}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Sorted Rows", 
            {"Employee ID", "Standard ID"}, 
            {
                {
                    "All Rows", 
                    each 
                        let
                            varTopTwo = Table.FirstN(_,2)
                        in
                        Table.AddColumn(
                            Table.AddColumn(
                                varTopTwo,
                                "Score Total",
                                each Table.RowCount(Table.SelectRows(varTopTwo, each [Score] >= .9)) = 2
                            ),
                            "Valid Batch",
                            each not List.ContainsAny(varTopTwo[Batch Type], {"Formal-OffJob", "Formal-OnJob"})
                        ),
                    type table [Employee ID=nullable text, Standard ID=nullable text, Date=nullable date, Score=nullable number, Batch Type=nullable text, Score Total= Logical.Type, Valid Batch = Logical.Type]
                }
            }
        ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Score", "Batch Type", "Score Total", "Valid Batch"}, {"Date", "Score", "Batch Type", "Score Total", "Valid Batch"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded All Rows", each ([Score Total] = true) and ([Valid Batch] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Employee ID", "Standard ID", "Date", "Score"})
in
    #"Removed Other Columns"

As you can see, all of the heavy lifting is done in the Table.Group step.

It groups based on the IDs and puts all other data in "all rows" - a nested table.
Then I keep only the first two rows and store that in a varTopTwo temporary table.

That looks like this:

The Score Total makes sure both Scores are >= .90. For Employee/Standard ID that is TRUE.

The Valid Batch makes sure tahat Formal-OffJob/OnJob are not in the batch type. For #3, that is FALSE.

I expand that "All Rows" column and get this:

Then I just filter Score Total and Valid Batch to both be true.

To use my code, see this:

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

If that is not what you want, again, provide usable data per below.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[SP7273]

Thanks Edhans for the information and you're time.

I will look into this today and see if I can get it working.

one question will the date format be a issue? using Australian format.

[edhans]

No. The core code should work fine when you localize it on your machine. My sample code may not work for the date type though. You would need to change the Change Type step in my code to this:

= Table.TransformColumnTypes(#"Changed Type with Locale",{{"Score", Currency.Type}, {"Date", type date}}, "en-US")

Or redo the dates in your local format first.