Merging tables query

[learner03]

I have below 2 tables-SO and SO_lines

I want the output table as such that the new table should only contain those SO that are in SO table and the quantity column should be the the sum of only quantities of those items that are in SO table. I do not want the items that do not belong to SO table.

Table SO
JobID
A
B
C
D
Table SO_lines
JobID	Item	Quantity
A	123	40
A	456	100
A	789	45
A	321	23
B	123	54
B	456	54
B	765	32
C	123	12
C	321	54
C	456	23
D	987	87
D	123	122
E	123	43
F	456	23

OUtput-items and quantites of only those who are in SO table-

Output Table
item	Quantity
123	228
456	177
789	45
321	77
765	32
987	87

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[sevenhills · Accepted solution]

Assuming your relatioship is like this

Create a table as

Add filter 

Your output will be 

[learner03]

@sevenhills how can I do the same in Power query?

[sevenhills]

For Power Query, 

a) Using Merge Queries - inner join type
a) 1) Check this link https://docs.microsoft.com/en-us/power-query/merge-queries-inner

a) 2) As per the link, your right table for merge is "Table SO"

a) 3) Join Kind is Inner Join

a) 4) You dont need to expand for any right table columns 

a) 5) remove unwanted columns

b) Select the column "item" , click group by Quantity

https://docs.microsoft.com/en-us/power-query/group-by