cancel
Showing results for
Did you mean:
Frequent Visitor

## Union two tables with different columns and sum the same register

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.

Thanks!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## Re: Union two tables with different columns and sum the same register

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support

## Re: Union two tables with different columns and sum the same register

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User III

## Re: Union two tables with different columns and sum the same register

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

Try my new Power BI game Cross the River
Frequent Visitor

## Re: Union two tables with different columns and sum the same register

Thanks a lot my friend

It works perfectly

Best Regards

Juan

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors