Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
HarryMay69
New Member

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 numberOrder OperationTotal Operation Time HoursOperator Booked Date Time Booked HoursKeep
WA12345110Andy01/05/20232 
WA12345110John02/05/20238 
WA1234615Andy03/05/20235Keep
WA1234712Bill03/05/20231 
WA1234712Paul03/05/20232 
WA1234721Andy04/05/20231Keep
WA1234822Andy06/05/20232 
WA1234822John06/05/20231 
WA1234912John07/05/20231Keep
WA1234912John08/05/20231Keep
WA1235112John09/05/20234 
WA1235112Paul10/05/20235 

 

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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:

BA_Pete_0-1683800599688.png

 

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

BA_Pete_1-1683800698156.png

 

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

BA_Pete_0-1683801046690.png

 

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:

BA_Pete_3-1683800877259.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
HarryMay69
New Member

Hi Pete

 

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

BA_Pete
Super User
Super User

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:

BA_Pete_0-1683800599688.png

 

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

BA_Pete_1-1683800698156.png

 

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

BA_Pete_0-1683801046690.png

 

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:

BA_Pete_3-1683800877259.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors