Trying to compare data from 2 disconnected tables, please help me to find an algorithm!

[olivierhbh]

Hello,


I've been struggling for a day trying to figure out how to solve this problem. Here is what I have:


Let's consider these 2 tables to simplify:
TABLE 1
[TABLE="width: 194"]
<tbody>[TR]
[TD]charter[/TD]
[TD]cost[/TD]
[TD]value1[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]1[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]2[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]3[/TD]
[TD]32[/TD]
[/TR]
</tbody>[/TABLE]

TABLE 2
[TABLE="width: 189"]
<tbody>[TR]
[TD]charter[/TD]
[TD]cost[/TD]
[TD]value2[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]1[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]3[/TD]
[TD]62[/TD]
[/TR]
</tbody>[/TABLE]

I can't create a new table from these 2 as they are pulled from different databases.


Ideally, I would like to show this table in a pivot:


[TABLE="width: 254"]
<tbody>[TR]
[TD]charter[/TD]
[TD]cost[/TD]
[TD]value1[/TD]
[TD]value2[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]1[/TD]
[TD]57[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]2[/TD]
[TD]56[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]62[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]3[/TD]
[TD]32[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

But maybe something like this would be more realizable, and would be ok for me as well (considering that all charters from table 2 are included in table 1)

[TABLE="width: 693"]
<tbody>[TR]
[TD]charter&cost (table1)[/TD]
[TD]charter&cost (table2)[/TD]
[TD]value1[/TD]
[TD]value2[/TD]
[/TR]
[TR]
[TD](blank)[/TD]
[TD]b3[/TD]
[TD][/TD]
[TD]62[/TD]
[/TR]
[TR]
[TD]a1[/TD]
[TD]a1[/TD]
[TD]57[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]b2[/TD]
[TD][/TD]
[TD]56[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c3[/TD]
[TD][/TD]
[TD]32[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What's interesting for me is to be able to identify which costs from table 2 are not present in table 1. In this last example I would conclude that cost 3 is not declared in table 1 for charter b.

I'd really appreciate your help.

Thanks in advance,
Olivier.

 

[Useful]

Hello,
Power Query is the better solution in this case for combining (also such as transform, refine and etc) the tables. Get the tables with the PQ and use Home/Combine/Append

 

[olivierhbh]

I thought about trying to find a solution with power query but there is so much work done within this file that I can't redo it all with pq... I'm trying to find a solution that would work just with PP.
Thanks for your answer.

 

[Useful]

merging tables in power pivot possible with creating relationships, using functions - for example Relate(), LOOKUPVALUE() or SQL. But in your case it is so different not?

 

[olivierhbh]

Yes it is, I arrived close to the result with those functions, but not close enough. I'll try to see if I can include a "side" power-query constructed table in the file.
Thanks for your help.