Forum Discussion
Merging tables query
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 |
Assuming your relatioship is like this
Create a table as
Add filter
Your output will be
4 Replies
Assuming your relatioship is like this
Create a table as
Add filter
Your output will be
sevenhills how can I do the same in Power query?
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-innera) 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
