Merging two tables with two common dimensions

[ratherbegliding]

Dear all,

Greetings! This is my first post

So I'm having a problem but rather than get into a wordy explanation I'll just dive into a basic example.

Think fishing trips.

In table 1 we have:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Trip No[/TD]
[TD]Species[/TD]
[TD]No of fish[/TD]
[/TR]
[TR]
[TD]FT001[/TD]
[TD]TUNA[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]FT001[/TD]
[TD]HAKE[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]FT001[/TD]
[TD]COD[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]FT002[/TD]
[TD]TUNA[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]FT002[/TD]
[TD]COD[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]


Then in table 2 we have the weights caught:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Trip No[/TD]
[TD]Species[/TD]
[TD]KG[/TD]
[/TR]
[TR]
[TD]FT001[/TD]
[TD]TUNA[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]FT001[/TD]
[TD]HAKE[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]FT001[/TD]
[TD]COD[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]FT002[/TD]
[TD]TUNA[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]FT002[/TD]
[TD]COD[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]


So each combination of Trip No and Species has a specific value in each table.
What I'm trying to achieve is a combined table like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Trip No[/TD]
[TD]Species[/TD]
[TD]No of fish[/TD]
[TD]KG[/TD]
[/TR]
[TR]
[TD]FT001[/TD]
[TD]TUNA[/TD]
[TD]5[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]FT001[/TD]
[TD]HAKE[/TD]
[TD]10[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]FT001[/TD]
[TD]COD[/TD]
[TD]20[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]FT002[/TD]
[TD]TUNA[/TD]
[TD]10[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]FT002[/TD]
[TD]COD[/TD]
[TD]20[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]



I've been trying to do this with Power Pivot but I keep running into problems with the many to many type relationship of each dimension, getting the message:

'The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values.'

every time I try to make connections between the tables. I've tried to use bridging tables such as a table with just species and another with just unique, sequential trip no's but haven't managed to get the relationships to work.

Does anyone have any suggestions how to do this in Power Pivot?
Using Excel 2010.

Thanks,

RBG

 

[Ozeroth]

I would suggest you created a 'joined' table using Power Query and load the resulting table to Power Pivot (free add-in for Excel 2010 if you haven't already installed it).
You can create queries for the two original tables, then use Power Query's Merge function (which creates a join between the tables) where you can select matching columns from each table.

 

[ratherbegliding]

Yes!
That did it.
Many thanks.