Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |