Forum Discussion
Comparing Two Sales Tables
Hi Guys, Hope you are doing well.
I am working on a report in this report I have two sales tables one is from SAP and Another is from Salesforce. And I have find match mismatch between both of them.
1st Approach :- I am merging them in power query editor based on sales order columns from both,But the problem is in that I have duplicate orders becoz of multiple products for same orders in both tables and in that case I am geeting extra line while merging. eg (first table rows 4, 2nd 4 rows so. 4*3=12) rows. But here should get 4 rows max.
2nd Approach :- I have made a composite key by merging sales order & product code colum in both tables now i am merging them again based on combined key here all result is good but i am missing the data where my product code is not matching However I want to show both matched unmatched in same table.
Desire result :-
I want both matched unmatched orders in same table and want all orders to show along with the amount, product code from both tables so I can compare further differences.
Sales OrderProduct Code (SAP)Amount (SAP)Product Code (SF)Amount (SF)Match Type
| SO101 | PRD001 | 5,000 | PRD001 | 5,000 | ✅ Full Match |
| SO101 | PRD002 | 3,200 | PRD008 | 3,000 | ❌ Product Code Mismatch |
| SO102 | PRD003 | 2,500 | PRD009 | 2,400 | ❌ Product Code Mismatch |
| SO103 | PRD004 | 4,000 | PRD004 | 4,100 | ⚠️ Amount Mismatch |
| SO104 | PRD005 | 1,800 | — | — | ❌ Missing in SF |
| SO104 | PRD006 | 2,200 | PRD006 | 2,200 | ✅ Full Match |
| SO105 | — | — | PRD007 | 2,800 | ❌ Missing in SAP |
Thanks in Advance
Data as an example SAP
Sales Order | Product Code | Amount (SAP) |
SO101 | PRD001 | 5,000 |
SO101 | PRD002 | 3,200 |
SO102 | PRD003 | 2,500 |
SO103 | PRD004 | 4,000 |
SO104 | PRD005 | 1,800 |
SO104 | PRD006 | 2,200 |
Sales Force
Sales Order | Product Code | Amount (SF) |
SO101 | PRD001 | 5,000 |
SO101 | PRD008 | 3,000 |
SO102 | PRD009 | 2,400 |
SO103 | PRD004 | 4,100 |
SO105 | PRD007 | 2,800 |
SO104 | PRD006 | 2,200 |
Hi Gurpreetsingh1 ,
Thank you for reaching out to the fabric community. We have reviewed your scenario and implemented the logic to classify the records based on Product Code and Amount comparisons between the Salesforce and SAP tables.
FYI:
Based on our testing, the logic is working as expected. Please refer to the Match Type column in the result to view the classification.
For better understanding, I’ve attached the PBIX file. Kindly go through it, it demonstrates the logic and output based on your scenario.
Thank you for your response, m4ni & Deku .
If my message helped solve your issue, please mark it as Accepted Solution. If it was helpful, consider giving it a Kudos.
7 Replies
I would union them. Then group by all three columns, and count the number of rows. Those with a count of 2 are the same between the two sources. Those with a 1 only exist in one source or the amount differences between sources.
There are always different ways to achieve this. I prefer the DAX soution as its easier for me at least.
If you have the composite key, keep that.
Create a table in DAX as the following:
Compare =VAR Table1 = SUMMARIZECOLUMNS(Table1[Key])VAR Table2 = SUMMARIZECOLUMNS(Table2[Key])RETURN INTERSECT(Table1, Table2)This will give you eveything which matches between the two tables.For the unmatched rows, you need to decide whch table is the primary and secondary for the comparison.If table 1 is the primary or master then use this code:Compare =VAR Table1 = SUMMARIZECOLUMNS(Table1[Key])VAR Table2 = SUMMARIZECOLUMNS(Table2[Key])RETURN EXCEPT(Table1, Table2)otherwise switch the tables around in the EXCEPT.This will give you eveything in the first table which is NOT in the second table.Hope that helps. Please remember to mark as solved if so.Hi m4ni Thanks for the help, but i need both the matched & unmatched orders in same table along with other columns.
Eg Desired Result
Sales Order Product Code (SAP) Amount (SAP) Product Code (SF) Amount (SF) Match Type
SO101 PRD001 5,000 PRD001 5,000 ✅ Full Match SO101 PRD002 3,200 PRD008 3,000 ❌ Product Code Mismatch SO102 PRD003 2,500 PRD009 2,400 ❌ Product Code Mismatch SO103 PRD004 4,000 PRD004 4,100 ⚠️ Amount Mismatch SO104 PRD005 1,800 — — ❌ Missing in SF SO104 PRD006 2,200 PRD006 2,200 ✅ Full Match SO105 — — PRD007 2,800 ❌ Missing in SAP Hi Gurpreetsingh1 ,
Thank you for reaching out to the fabric community. We have reviewed your scenario and implemented the logic to classify the records based on Product Code and Amount comparisons between the Salesforce and SAP tables.
FYI:
Based on our testing, the logic is working as expected. Please refer to the Match Type column in the result to view the classification.
For better understanding, I’ve attached the PBIX file. Kindly go through it, it demonstrates the logic and output based on your scenario.
Thank you for your response, m4ni & Deku .
If my message helped solve your issue, please mark it as Accepted Solution. If it was helpful, consider giving it a Kudos.
Hi Gurpreetsingh1 ,
I hope the previous response was helpful. For better understanding, please check the attached PBIX file.
If this resolves your query, kindly mark it as an accepted solution it will help other community members facing a similar issue.
Thanks.
Hi Gurpreetsingh1 ,
Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.
Hi Gurpreetsingh1 ,
Please let us know if your issue has been resolved with the solution provided. If it has, marking it as the accepted solution would help others with similar questions. If you're still experiencing problems, please provide more details.
Thank You.
