Forum Discussion
Isolating Specifc Rows of data in a Table
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 |
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 expandDataPete
2 Replies
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 expandDataPete
Hi Pete
Amazing and simple if you know how, thanks very much Andy
