How to combine two table into one

[Henry1943]

I want to combine the two table as below:

Table 1

Product name	version	date	Quantity1	Quantity2
H123	1	7/7/2017	3
H123	2	7/8/2017	1
H124	1		1
H125	1	7/11/2017	3	3
H126	1	7/12/2017	2
H127	1		1
H128	1	7/15/2017	1	1

Table 2

Product name	version	date	Quantity1	Quantity2
H123	1	7/7/2017	3	3
H123	2		1	1
H124	1	7/9/2017	1	1
H125	1		3	3
H126	1		2	2
H127	1	7/14/2017	1	1
H128	1	7/15/2017	1	1

I want to get the table after combine as below

Product name	version	date	Quantity1	Quantity2
H123	1	7/7/2017	3	3
H123	2	7/8/2017	1	1
H124	1	7/9/2017	1	1
H125	1	7/11/2017	3	3
H126	1	7/12/2017	2	2
H127	1	7/14/2017	1	1
H128	1	7/15/2017	1	1

How could I get that?

[v-huizhn-msft · Accepted solution]

Hi @Henry1943,

1. I create a unique calculated column using the formulas. And create relationship between the two tables.

Column = Table1[Product name]&Table1[version]
Column = Table2[Product name]&Table2[version]

2. In table1, create calculated column using the formulas.

New Date = IF(ISBLANK(Table1[date]),RELATED(Table2[date]),Table1[date])
New Quantity2 = IF(ISBLANK(Table1[Quantity2]),RELATED(Table2[Quantity2]),Table1[Quantity2])

3. Create a new table by clicking "New Table" under Modeling on Home page.

Result = SELECTCOLUMNS(Table1,"Product name",Table1[Product name],"version",Table1[version],"date",Table1[New Date],"Quantity1",Table1[Quantity1],"Quantity2",Table1[New Quantity2])

Please see the expected result.



Please download the attached file to test.

Best Regards,
Angelia