Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Would Appending the tables help?
Can you give some example with dummy data ?
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
result
Check my file: Subtract multiple columns from two tables.pbix
If my code solves your problem, mark it as a solution
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.