Isolating Specifc Rows of data in a Table

Hi HarryMay69 ,

Group your table on [Order number] and [Order Operation], add one aggregate column that is SUM of Operator (will give an error, don't worry!), and add another that uses the ALL ROWS operator:

Once applied, you will get auto-generated code like this:

Change the code for the [operatorCount] aggregated column to this instead, noting also the change of output data type:

Once applied, you can expand the [data] column to reinstate all your original columns, and filter the table on [operatorCount] = 1 to quickly identify the rows you want to review.

Output:

Full example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZG9CsMwDIRfpXgOxJJ/M6Zju2TLEDIUGmghpF069O0rgwOKajzYxuI+30meJjX2gMY61ShIS9PWb/cvHRpa7VrUaOiCtE5qborA5fXYEoAMiALwGXDMwDB9ql+X5c2QkJHkfX6uq0RAWHD9cPv86WUPIdeARbLifREpZgQZ4isWXL9PyVda6FgLuz7UI5WQWEUcFJCOIfaY6qDPgwUt/o708w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order number" = _t, #"Order Operation" = _t, #"Total Operation Time Hours" = _t, #"Operator " = _t, #"Booked Date " = _t, #"Time Booked Hours" = _t, Keep = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Order number", type text}, {"Order Operation", Int64.Type}, {"Total Operation Time Hours", Int64.Type}, {"Operator ", type text}, {"Booked Date ", type date}, {"Time Booked Hours", Int64.Type}, {"Keep", type text}}),

    groupOrderOperation = Table.Group(chgTypes, {"Order number", "Order Operation"}, {{"operatorCount", each List.Count(List.Distinct([#"Operator "])), type nullable number}, {"data", each _, type table [Order number=nullable text, Order Operation=nullable number, Total Operation Time Hours=nullable number, #"Operator "=nullable text, #"Booked Date "=nullable date, Time Booked Hours=nullable number, Keep=nullable text]}}),
    expandData = Table.ExpandTableColumn(groupOrderOperation, "data", {"Total Operation Time Hours", "Operator ", "Booked Date ", "Time Booked Hours", "Keep"}, {"Total Operation Time Hours", "Operator ", "Booked Date ", "Time Booked Hours", "Keep"})

in
    expandData

Pete