Forum Discussion

sglendenning's avatar
sglendenning
Helper II
6 years ago
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 ...
Need Help
sglendenning's avatar
sglendenning
Helper II
6 years ago

Table1

DateSalesRepAttemptsContactsSales
202004Joe100105
202004Sue1502010
202003Joe200202
202003Sue503020

 

Table2

DateSalesRepAttemptsContactsSales
202004Joe1051
202004Sue511
202003Joe2052
202003Sue410
     

 

I need to subtract Table1 from Table2 by date and SalesRep.  So output would be;

DateSalesRepAttemptsContactsSales
202004Joe9054
202004Sue145199
202003Joe180150
202003Sue462920
    • sglendenning's avatar
      sglendenning
      Helper II
      6 years ago

      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's avatar
        ziying35
        Impactful Individual
        6 years ago

        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

  • ziying35's avatar
    ziying35
    Impactful Individual
    6 years ago
    // 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