Subtract multiple columns from two tables

[sglendenning]

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 the other.  I'm trying to use power query but am stumped as to the best solution.

[Greg_Deckler]

Would Appending the tables help?

[camargos88]

@sglendenning ,

Can you give some example with dummy data ?

[sglendenning]

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

[ziying35]

// 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
    result

Check my file:  Subtract multiple columns from two tables.pbix 

If my code solves your problem, mark it as a solution

[camargos88 · Accepted solution]

Hi @sglendenning ,

Check this file: Download PBIX 

[sglendenning]

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?

[ziying35]

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