Forum Discussion
Solved
Subtract multiple columns from two tables
I have two tables organized by MMYYYY and sales rep with the exact same data columns for attempts, contacts, sales (and 20 other data columns) but need to subtract the same columns from one table to ...
Table1
| Date | SalesRep | Attempts | Contacts | Sales |
| 202004 | Joe | 100 | 10 | 5 |
| 202004 | Sue | 150 | 20 | 10 |
| 202003 | Joe | 200 | 20 | 2 |
| 202003 | Sue | 50 | 30 | 20 |
Table2
| Date | SalesRep | Attempts | Contacts | Sales |
| 202004 | Joe | 10 | 5 | 1 |
| 202004 | Sue | 5 | 1 | 1 |
| 202003 | Joe | 20 | 5 | 2 |
| 202003 | Sue | 4 | 1 | 0 |
I need to subtract Table1 from Table2 by date and SalesRep. So output would be;
| Date | SalesRep | Attempts | Contacts | Sales |
| 202004 | Joe | 90 | 5 | 4 |
| 202004 | Sue | 145 | 19 | 9 |
| 202003 | Joe | 180 | 15 | 0 |
| 202003 | Sue | 46 | 29 | 20 |
// QueryOut
let
Source = Table1&Table2,
funcLists = List.Transform(List.Skip(Table.ColumnNames(Source),2),each {_,(x)=>let lst=Table.Column(x,_) in lst{0}-lst{1}}),
result = Table.Group(Source,{"Date","SalesRep"},funcLists)
in
resultCheck my file: Subtract multiple columns from two tables.pbix
If my code solves your problem, mark it as a solution