Joining data without duplicating original data count

[HLJones]

Hi All,

I have three data tables, the first is a list of all ProjectIDs and their total spend, the second is a list of people working on the projects (multiple people on each project) and the third is a list of equipment that some projects use.

I want to join all three data sets without duplicating the original number of Projects each time an additional staff member or equipment is added in.

The kinds of questions we might want to ask are: "how many Projects are there? How many Projects had Mr A working on them? How many Projects had equipment item 1234 on it? What was the total cost of all Equipment Items on Projects with an Officer staff member?" etc.

The output needs to be a Tableau data extract which we will then build visual interrogations with.

Please can someone advise? I suspect it is something to do with Transpose then Cross Tab but i never quite get what i'm looking for.

The other element is this needs to be scalable. In this example there are 338 Projects, each with multiple staff, but we would need to run this for up to 18000 projects, again each with many staff members.

Please see workbook attached and thank you so much in advance!

Holly

[Raj]

@HLJones 
changes made to unique tool
please check is this what was required?

[HLJones]

Thanks Raj. Unfortunately that doesn't work as I lose data. If I unique just on Project ID then only one staff member/equipment item is pulled through. I need all data but a distance count and sum per Project ID, without losing each individual named staff member or equipment.

[Deano478]

Hey @HLJones what about situations where a project is not using any equipment? 

[HLJones]

Hi Deano478, if there was no equipment on the project, we still want to include that project in our analysis. It just won't show up in questions around specific equipment, but we will need to answer questions like "how many projects had any equipment // how many had none?"