How to filter SharePointList in order only the next (smallest) not yet completed working step is displayed in gallery

Question

I have a large SharePointList. The SharePoint list looks as follows:

PurchaseOrder	WorkingStep	Technology	Completed
1	1	milling	false
1	2	milling	false
2	1	milling	true
2	2	drilling	false
3	1	drilling	true
3	2	cutting	false
3	2	milling	false
3	3	drilling	false
999	1	drilling	true
999	2	milling	true

The SharePointList consists of several purchase orders which contain multiple working steps. Most of the purchase orders are already finished (Completed = true). This already reduces the sharepoint list drastically.

How can I filter a gallery (Property: Items) in order to see only the WorkingSteps that have not yet been completed ( Filter(SharePointList, Completed = false) ) AND which are the smallest WorkingStep within their PurchaseOrder that have not yet been completed?

For example working step 3 of purchase order 3 is at the present not relevant for me because there are two working steps before which have to be done. Working step 1 of purchase order 3 is already completed.

This should result in a gallery that only contains the entries in bold (see table above).

Answer 1

You could use GroupBy and then only show the first item. Try something similar to:

With({
  spListIncomplete: SortByColumns(
    Filter(SharePointList, Completed = false), 
    "PurchaseOrder", SortOrder.Ascending, "WorkingStep", SortOrder.Ascending
  )},
  GroupBy(spListIncomplete, "PurchaseOrder", "Steps")
)

Then in your gallery, only show the first item in the group. For example:

First(ThisItem.Steps).WorkingStep

First(ThisItem.Steps).Technology

PurchaseOrder won't be in the grouped items, so:

ThisItem.PurchaseOrder