Combine tables

[sharshra]

I have to combine 2 tables & create a consolidated table to reflect data from both tables. If the names are same, it should add the qty. Otherwise, it should add a new row. Final combined table should be sorted by the name.

I tried using power query merge, but not getting the correct results. Can the experts suggest how this can be done please?

For example consider the following tables.

excel problems.xlsx
	C	D
2	Name	Qty
3	bcd	2
4	de	19
5	fghi	954
Sheet9

excel problems.xlsx
	B	C
2	Name	Qty
3	abc	56
4	xyz	37
5	fghi	6
6	bcd	10
7	mnop	88
Sheet10

The final combined table should be like this -

excel problems.xlsx
	B	C
2	Name	Qty
3	abc	56
4	bcd	12
5	de	19
6	fghi	960
7	mnop	88
8	xyz	37
Sheet11

 

[lrobbo314]

Like this.

Book3
	A	B	C	D	E	F	G	H
1	Name	Qty		Name	Qty		Name	Qty
2	bcd	2		abc	56		bcd	12
3	de	19		xyz	37		de	19
4	fghi	954		fghi	6		fghi	960
5				bcd	10		abc	56
6				mnop	88		xyz	37
7							mnop	88
Sheet1

let
    Source = Table.Combine({Table1,Table2}),
    Group = Table.Group(Source, {"Name"}, {{"Qty", each List.Sum([Qty]), type number}})
in
    Group

 

[Cubist]

With GROUPBY function:

=LET(v,VSTACK(Sheet9!C3:D5,Sheet10!B3:C7),GROUPBY(INDEX(v,,1),INDEX(v,,2),SUM,,0))

 

[sharshra]

With GROUPBY function:

=LET(v,VSTACK(Sheet9!C3:D5,Sheet10!B3:C7),GROUPBY(INDEX(v,,1),INDEX(v,,2),SUM,,0))

Thanks, @Cubist. Unfortunately, my excel version doesn´t have GROPBY function. Is there any alternative to get the same result?

 

[Cubist]

Is there any alternative to get the same result?

Try:

=LET(v,VSTACK(Sheet9!C3:D5,Sheet10!B3:C7),u,UNIQUE(INDEX(v,,1)),SORT(HSTACK(u,MAP(u,LAMBDA(m,SUM(FILTER(INDEX(v,,2),INDEX(v,,1)=m)))))))

 

[sharshra]

Great!!! Thanks, @Cubist

Thanks to @lrobbo314 also

 

[Fluff]

@sharshra in future please do not mark your post saying it works as the solution. You need to mark the post that actually contains the solution.