merge queries

[IrenaG]

Hello, I have 3 tables: 

In table 1 I have the sum price of the product that name is 'ORANGE". The number of product is 201619038.

name	number	sum
orange	201619038	95,608.13

In table 2 I have the sum price of the same product that name  'ORANGE" but it appears in two lines. The total result is the same 95,608.13.

name	number	sum
orange	96395665	91,575.50
orange	967844	4,032.63
total		95,608.13

In table 3 I have Conversion Table for number of product:

name	the number in table 1	the number in table 2
orange	201619038	96395665
orange	201619038	967844

The question is: how can I do the merge of table 1 & 2 if I have to get to the result like this:

name	from table 1number	sum	from table 2 number	sum	difference
orange	201619038	95,608.13	96395665	91,575.50	4,032.63
orange	201619038		967844	4,032.63	-4,032.63

Thank you in advance

[Anonymous]

The short answer is you can't get exactly what you described in your last entry.  You can get a table that is pretty close, but the Sum column will repeat.

The best you could do is start with Table 3 and merge in the contents of 1 and 2.

Alternatively you could bring all 3 tables into Power BI and create relationships from 3 -> 1 and 3 -> 2, then build the table inside your visuals instead, using Table 3 as the row headers.

[IrenaG · Accepted solution]

thank you, I will try to fix it