Showing All Items With Shared Parent

I'm running into a problem where I'm not sure how to efficiently solve. I have tables for Sales, Order, and Pipeline along with a table of part numbers which is used to be able to filter the 3 tables by part number through the same search interface:

I want to create a similar view to which shows all items on each order that the part number appears on. This shows how my report works now vs what I want to show:

One approach I think should work would be creating a column ("Key") with [Part Number]&[Order] to act as a key for each bullet shown above, then add another column ("OrderKey") where I concatenate all keys for each, creating a field with data that looks like:

This way, I could search the OrderKey column for "Part Number 1" and it would show all of the sub-items. I would create a new table append the OrderKey values from Sales, Orders, and Pipeline to get a complete table that I can use to filter all 3 tables at once.

Is there a better/more elegant way to handle this? It seems like this is a brute-force approach where there's likely another way to solve it.

Thanks in advance.