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 |
Thank you, I'm new to Power Query so much appreciated. What does the {0} do at the end of the formula? Can I ask how you would write within the formula if there is no value for the rep in Table2 how you would return the value in Table1?
Hi, sglendenning
I merged the two tables directly and then grouped them, "{0}, {1}" being the table1 and the table2 corresponding to the values in each column. I didn't consider that rep in Table2 has no value. You can simulate the new data. Let me see how to realize it.
My text is machine translated, I wonder if the translation can make you understand my meaning
