Forum Discussion
How to combine two table into one
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?
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
1 Reply
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