Union two tables with different columns and sum the same register

[JuanVR10]

Hi guys,

I have the following problem. I have two tables (Table1, Table 2) with this structure:

Table 1 have these fields: (Material, Desripción, Sales and Stock)

Table 2 have these fields: (Material, Descripción, Sales, Store and TT)

As you can see both aren´t identical table. So i need to join the two in a new table, but i have to include both materials in both tables and sum the Sales when the material is the same. For those columns when doesn´t exists in the other table, simply put null.

I´m trying to do it with Merge, but put the Material in different columns.

Could u help me please?

Thanks!!!

[Zubair_Muhammad]

@JuanVR10 

Seems you are doing a merge query when you ought to be doing an Append

[v-lili6-msft · Accepted solution]

HI, @JuanVR10 

You could try these two ways as below:

1. use DAX to create a calculate table by this formula:

Dax table = VAR _UNIONTABLE=UNION(SELECTCOLUMNS(Table1,"Material",[Material],"Descripcion",[Descripcion],"Sales",[Sales],"Store",BLANK(),"Stock",[Stock],"TT",BLANK()),
SELECTCOLUMNS(Table2,"Material",[Material],"Descripcion",[Descripcion],"Sales",[Sales],"Store",[Store],"Stock",BLANK(),"TT",[TT])) RETURN
GROUPBY(_UNIONTABLE,[Material],[Descripcion],"Sales",SUMX(CURRENTGROUP(),[Sales]),"Store",MAXX(CURRENTGROUP(),[Store]),"Stock",SUMX(CURRENTGROUP(),[Stock]),"TT",SUMX(CURRENTGROUP(),[TT]))

Result:

2. In Edit Queries, Use Append function and group by function to create a new table

Result:

here is pbix file, please try it.

Best Regards,

Lin

[JuanVR11]

Thanks a lot my friend

It works perfectly

Best Regards

Juan