Isolating Specifc Rows of data in a Table

[HarryMay69]

Hi All

I am strugling to isolate specic rows of data in a table I use daily.

We have jobs listed on orders that have staged operations on them, each operation has a set time in hours.

Any operator can book working time against the operations on any day.

What I am trying to find is the rows data where only one operator has booked on one operation even if the operator has booked more than once on different days.

Please see an exampl table below, some rows have the word "Keep" in, these are the rows of cells I wish to keep to review further.

Please note the Total Operations Time Hours value is repated in the table each time a booking is added by a differnet operator or on a different day, but it is a single value.

Order number	Order Operation	Total Operation Time Hours	Operator	Booked Date	Time Booked Hours	Keep
WA12345	1	10	Andy	01/05/2023	2
WA12345	1	10	John	02/05/2023	8
WA12346	1	5	Andy	03/05/2023	5	Keep
WA12347	1	2	Bill	03/05/2023	1
WA12347	1	2	Paul	03/05/2023	2
WA12347	2	1	Andy	04/05/2023	1	Keep
WA12348	2	2	Andy	06/05/2023	2
WA12348	2	2	John	06/05/2023	1
WA12349	1	2	John	07/05/2023	1	Keep
WA12349	1	2	John	08/05/2023	1	Keep
WA12351	1	2	John	09/05/2023	4
WA12351	1	2	Paul	10/05/2023	5

[HarryMay69]

Hi Pete

Amazing and simple if you know how, thanks very much Andy

[BA_Pete · Accepted solution]

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