Filtering problem on combined data

[sacred_hoops]

Hello - I have a problem with the way my data exists that doesn't allow me to filter what I want. I suspect this problem has been enocuntered before, but my normal soultion of Googling the problem isn't working as I don't quite know how to phrase what the problem is!

I'll try my best to explain....

Here is an example simplifed version of my data. The two tables have a one-to-many relationship that allows me to filter on the staff 

Job Table A

Job #	location	Staff
1	London	Steve
1	London	Engineer
2	New York	Pablo
2	New York	Engineer
3	Paris	Tim
3	Paris	Engineer
4	Madrid	Steve
5	Glasgow	Tim
6	Miami	Pablo

Reference Table B (Unique values)

Name	Type ID
Steve	2
Tim	2
Pablo	2
Engineer	240

As you can see, In the Job table I have jobs with staff & 'Engineer' assigned (Jobs 1,2.3), and jobs with only staff assigned (4,5,6). 

What I want to achieve, is to filter on jobs with an 'Engineer' assigned, and return the list of jobs (1,2,3) with the staff (Steve, Tim, 

In it's current relationship, by filtering on 'Engineer' I lose the records of the staff from the job table.

What is the best way to model my data to achieve this?

In the reference table I have a type ID that distinguishes between staff as they have a different Type ID if that can be used. 

Thank you in advance for any support you can offer.

Thanks, 

Tom

[BA_Pete · Accepted solution]

Hi @sacred_hoops ,

You can group your jobs into nested tables then evaluate for the presence of "Engineer" over each table, something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLJz0vJzwMygktSy1KVYnXQhF3z0jPzUlOLwDJGQAG/1HKFyPyibCAzIDEpJx+bBIomY7DKosxiIB2SmYshhqLYBCjgm5hSlJmC4iRTIM89J7E4Pb8cyRQzkOLMxNxMhFtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [JobNumber = _t, location = _t, Staff = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"JobNumber", Int64.Type}, {"location", type text}, {"Staff", type text}}),
    groupJobNumber = Table.Group(chgTypes, {"JobNumber"}, {{"data", each _, type table [JobNumber=nullable number, location=nullable text, Staff=nullable text]}}),
    addHasEngineer = Table.AddColumn(groupJobNumber, "hasEngineer", each if List.Contains([data][Staff], "Engineer") then "hasEngineer" else null),
    expandDataCol = Table.ExpandTableColumn(addHasEngineer, "data", {"location", "Staff"}, {"location", "Staff"})
in
    expandDataCol

Example query output:

This can easily be expanded to create a more generic flag for identifying group members other than "Engineer".

Pete