Create new table using DAX from 2 tables with different number columns

[pang22]

Hello,

I have 2 tables (Table 1 with 6 columns & Table 2 with 5 columns) loaded in PowerBI desktop.

How to write a DAX to create a new table to sum the qty by month, factory, area and type in PowerBI? Thanks

I used the UNION but the PowerBI said the number of columns are different and cannot combine togehter.

Table 1 

Order Qty	Factory	Loading month	Type	Area	Cost
3	QN	2023/11	A	China	2
5	QN	2023/11	A	China	5
1	QB	2023/12	A	HK	6
2	QB	2023/12	A	HK	2

Table 2

Balance Qty	Suggested Factory	Year&Month	Type	Area
2	QB	2023/11	B	HK
3	QN	2023/12	B	HK
5	QN	2023/12	B	China
4	QB	2023/11	B	China

 Desire outcome: Combine Table 1 & 2 with below 5 columns only, sum the "Order Qty" & "Balance Qty" as "Combine Qty" 

Month	Combine Factory	Area	Type	Combine Qty
2023/11	QN	China	A	8
2023/12	QB	HK	A	3
2023/11	QB	HK	B	2
2023/11	QB	China	B	4
2023/12	QN	HK	B	3
2023/12	QN	China	B	5

[DimaMD · Accepted solution]

hi @pang22  is this your desired outcome? Maybe I specified the sequence of columns incorrectly, but the concept of joining two tables is as follows

t1ANDt2 = 
VAR t1 = SUMMARIZE('Table 1','Table 1'[Loading month],'Table 1'[Type],'Table 1'[Factory],'Table 1'[Area],"qty", SUM('Table 1'[Order Qty]))
var t2 = SUMMARIZE('Table 2','Table 2'[Year&Month],'Table 2'[Type],'Table 2'[Suggested Factory],'Table 2'[Area], "qty", SUM('Table 2'[Balance Qty]))
VAR t3 = UNION(t1,t2)
return
t3